Intermediate Intermediate graduation project

Project: Build an electric vehicle mileage and cost calculator

20 minutes · 8 steps · free, no sign-up

Everything from the Intermediate level goes into one genuinely useful tool. You are given a log of six charging trips with an odometer reading, energy used and where the car was charged. You will build the calculator: distance, cost from a price list, efficiency, totals, running cost per mile, and a flag for trips worth a closer look.

In this lesson you will

Calculate distance from odometer readings
Look up electricity prices with VLOOKUP and a locked table
Summarise by condition with SUMIFS and COUNTIFS
Flag trips with IF and OR
Test a what-if by changing one price

Quick referenceFormulas you will use

=C2-B2                                    miles driven
=D2*VLOOKUP(E2,$A$16:$B$18,2,FALSE)       cost of the charge
=ROUND(F2/D2,2)                           miles per kWh
=SUMIFS(G2:G7,E2:E7,"Public")             spend by charger type
=COUNTIFS(H2:H7,"<3.4")                   count of weak trips
=IF(OR(H2<3.4,E2="Rapid"),"Review","OK")  flag

The key ideasSummary: Project: Build an electric vehicle mileage and cost calculator

This calculator separates inputs (odometer, kWh, charger type and the price table) from calculations. The price table is the key design choice: because costs look prices up from one place, changing a tariff in a single cell updates every trip and every total. That is the habit that makes spreadsheets reliable.

The locked table reference $A$16:$B$18 is what lets one formula be filled down a whole column, and the FALSE in VLOOKUP makes sure a charger name must match exactly. SUMIFS and COUNTIFS then answer the questions you would otherwise filter and add up by hand.

Make it your own. Replace the sample trips with your own charging log and tariffs, adding rows to each range. Add a column for the date, and use a conditional format to shade the Review rows.

Watch out forCommon mistakes

Not locking the price table. A16:B18 slides down as you fill, and by the third row the lookup is searching empty cells and returns #N/A. Use $A$16:$B$18.
Leaving out FALSE. Without it VLOOKUP assumes the table is sorted and can quietly return the wrong price.
Typos in the charger name. "home" matches "Home" but "Home " with a trailing space does not. Keep chargers consistent, or tidy them with TRIM.
Mismatched ranges in SUMIFS. The range to add and the range to test must be the same height or you get #VALUE!.

Next stepTry it in the real Google Sheets

To adapt this for your own car, replace the six trips with your charging log and the price table with your tariffs, and extend the ranges as you add rows. Use Format → Conditional formatting to shade Review rows, and a column chart of cost per trip from Insert → Chart. If your readings are in kilometres, change the title to km/kWh. In real Sheets you could also use XLOOKUP instead of VLOOKUP, and a named range such as Prices in place of the cell address.

Keep going

More Intermediate lessons

Counting and adding with conditions Add up and count only the rows you care about with SUMIF and COUNTIF. Several conditions with SUMIFS and COUNTIFS Total or count rows that match more than one condition at once. Combining conditions: AND, OR and nested IF Make decisions with more than one test, and hand out grades with an IF inside an IF. IFS and SWITCH Replace long chains of nested IF with IFS, and match a code to a label with SWITCH. Cleaning up text: TRIM, PROPER, LEFT and FIND Fix messy names, pull a username out of an email address and join text back together. Splitting and joining text Pull a first and last name out of one cell, and join pieces of text back together.