Fuel Mileage Calculation

[guamlenahans]

Hi,
I have a table where i track fuel purchased for 7 vehicles. Vehicles are tracked by plate number. My columns are: Date, Vehicle, Gallons, Cost, Total Cost, and odometer. We enter them row after row and I pivot out the gallons for each vehicle. I'm trying to add two columns where I will factor in the miles driven and the miles per gallon. I will need the formula to look back at the last entry for the vehicle and calculate the miles driven between then and the latest fill up based on the odometer readings. I cant figure out how to do that.

Ideas?

Thanks

Rob

 

[guamlenahans]

Jason,

I got it to work, I should have just copied your formula over versus typing it in. Appreciate the help!

Rob

 

[KRice]

I think this is the working example @jasonb75 is describing

MrExcel_20200331.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Vehicle	GallonscOST		Total Cost	Odometer		Miles driven	Mileage
2	1-Jul	249JAN	15.83			43685
3	3-Jul	730DQZ	15.26			20411
4	3-Jul	818BAV	29.238			76928
5	1-Jul	398UHD	26.139			23543
6	8-Jul	368YOO	15.303			29285
7	17-Jul	398UHD	27.017			23684		141	5.218936
8	18-Jul	749FHT	26.424			37015
9	24-Jul	818BAV	28.29			77090		162	5.726405
10	25-Jul	730DQZ	14.303			20584		173	12.09536
11	25-Jul	398UHD	25.253			23843		300	11.87978
12	6-Aug	818BAV	29.123			77288		360	12.36136
13	8-Aug	398UHD	25.701			23965		422	16.41959
14	13-Aug	749FHT	18.77			37240		225	11.98721
15	14-Aug	478A76	12.847			169980
16	19-Aug	730DQZ	16.142			20829		418	25.89518
Sheet7

Cell Formulas
Range	Formula
H2:H16	H2	=IFERROR(F2-VLOOKUP(B2,B$1:F1,5,0),"")
I2:I16	I2	=IF(H2="","",H2/C2)

 

[guamlenahans]

well, kind of, some work and some don't. Oh well, thanks to all for the help.

 

[guamlenahans]

Like in the screen shot above, Cell 13. 398UHD/422 miles, but the difference in entries between that and
the last fill up (row 11) is 122 miles

 

[jasonb75]

The formula wasn't working originally because you had some extra $ symbols that were stopping it from dragging down correctly.

There is a major flaw with my suggestion though (I blame lack of sleep), I think that the suggestion in post #9 should return more accurate results than mine.