Forum Discussion
miles per gallon
I want to calculate miles per gallon from a column of mileage and a column of gallons?
4 Replies
You say "from a column of mileage and a column of gallons" but do you mean from a column of odometer readings and a column of gallons?
If the former, if you've already calculated how many miles were driven between fillings, you could just divide
=miles/gallons
If the latter, it would be
=(prior_reading - current_reading)/gallons
But for true and more accurate MPG calculation over the long haul it would be
=first odometer reading minus last odometer reading divided by total gallons since first reading.
I have a column of chronological odometer readings with the number of gallons since the previous odometer reading. I wanted to know if I could capture the formula at the top of the column that would automatically subtract the odometer readings and divide by the # of gallons.?
Entirely possible. The trick (or one of them) is that you need to have a formula that tracks how many rows of data there are. So I first create a table with two columns, Odo and Gal, to store the data.
I then write a formula, in cell E1, that retrieves the number of rows of data, =ROWS(Table1[Odo])
And then, using INDIRECT to create a dynamically changing formula, one that adjusts to the new rows of data, I write two formulas, the first to calculate miles driven between top row and bottom row,
=INDIRECT("A"&TEXT(E1+1,"0"))-A2
and, second, to calculate total gallons.
=SUM(B3:INDIRECT("B"&TEXT(E1+1,"0")))
and then just divide the former by the latter to get MPG
Of course, that could all be done in one more complex formula, but for the sake of helping you understand the steps, I thought doing it step by step made more sense.
See the attached spreadsheet. You can use it as it is, or modify to suit your own needs.
