Exceljet

Quick, clean, and to the point

Odometer gas mileage log

Excel formula: Odometer gas mileage log
Explanation 

To calculate gas (MPG) based on odometer readings you can build a table with a few simple formulas. In the example shown, the formulas in E5 and F5 are:

=[@Mileage]-SUM(C4) // E5 calculate mileage
=[@Distance]/[@Gallons] // F5 calculate mpg

How this example works

Note:  example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all data in an Excel Table called "data" to illustrate how Tables can make some formulas easier to maintain.

The formula in E5 subtracts the mileage in the row above from mileage in the current row to calculate distance (miles driven) since the last gas stop:

[@Mileage]-SUM(C4)

The SUM formula is used only to prevent errors on the first row, where the row above contains a text value. Without SUM, the first row formula will return the #VALUE error. The SUM function however will treat the text in C4 as zero and prevent the error.

The formula in F5 calculates MPG (miles per gallon) by dividing miles driven by gallons used:

=[@Distance]/[@Gallons]

The result is formatted as a number with one decimal place.

Summary stats

The formulas in I4:I6 calculate best, worst, and average MPG like this:

=MAX(data[MPG]) // best
=MIN(data[MPG]) // worst
=SUM(data[Distance])/SUM(data[Gallons]) // average

Because we are using an Excel Table to hold the data, these formulas will continue to show correct results as more data is added to the table.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.