Forum Discussion
miles per gallon
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.
- Thanks!