Looking for Mileage / MPG Tracking formula

[WayneB]

I'm trying to build a daily mileage spreadsheet that will allow me to enter
my daily mileage, and will use these numbers to calculate Miles Per Gallon on
the days when I fill up the tank (1-2 days per week).

So, column headings are as such:

A-----B---------C--------------D---------------E-----F
Date, Odometer, Miles Driven, Gallons Used, MPG, Average MPG
1/22, 0000
1/23, 0365
1/24, 0724
1/25, 0912, 912, 74, 12.32
1/28, 1215
1/29, 1526
1/30, 1797, 885, 76, 11.64
..
..
..

How could I put together a formula that will subtract the odometer reading
on 1/30 from the last fillup, whenever that was, to calculate the mpg?

Any information would be helpful.
Thanks!

 

[T. Valko]

Try this:

I would move your columns around slightly:

Column C = Gallons Used
Column D = Miles Driven

Place a 0 in cell C2 as your initial entry for Gallons Used

Then, enter this formula in cell D2 under Miles Driven: This is an array
formula and needs to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C2=0,"",B2-LARGE(IF(C$2:C2<>"",B$2:B2),2))

Enter this formula in E2 under MPG:

=IF(COUNT(C22)<2,"",D2/C2)

Format as NUMBER 2 decimal places

Select both D2 and E2 and copy down as needed.

Biff

 

[WayneB]

That seems to be doing the trick,

Thanks for your help!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

That seems to be doing the trick,

Thanks for your help!