Missing data in Excel

H

Howard Brazee

Date Start End Gallons Cost Miles Mileage Octane
....
7/16/2007 139486 139833 16.917 $50.73 347 21 85
8/2/2007 139833 140188 17.591 $50.12 355 21 85
8/17/2007 140188 17.166 $47.19 85
9/2/2007 141211 17.538 $50.67 87
9/27/2007 141211 18.002 $49.13 85
10/16/2007 17.707 $48.32 85

I have the above spreadsheet to keep car mileage. Trouble is,
sometimes we forget to write down mileage.

The simple code has:
B58 has 9/2/2007
C58 has =IF(B59>0,B59,"")
F58 has =IF(ISNUMBER(C58),IF(ISNUMBER(B58),C58-B58,""),"")
G58 has =IF(ISNUMBER(F58),F58/D58+0.5,"")

What I'd like to do is to go back one or more fill-ups, adding gallons
and miles to calculate a multiple fill-up mileage. e.g. F58 would
have 1023 miles and G58 divides this by 34.757 gallons to give 29.4
mpg.
e.g.
as if F58 had =IF(ISNUMBER(C58),IF(ISNUMBER(B57),C58-B57,""),"")
and if G58 had =IF(ISNUMBER(F58),F58/(D58+D57)+0.5,"")

I played around with nesting the above logic, but have been having
troubles handling all the possibilities.

What is a better way of handling this?
 
Top