Needing help with an equation for MPG on a sheet with multiple vehicles.

[Chaintinker]

I have a spreadsheet that is a log of fleet vehicles, how much gas they purchased, when they did this and the odometer reading at the time of purchase. Multiple vehicles are on this log.

I am looking for an equation that will search the range for a specific vehicle number then from the results subtract the smallest from the largest odometer reading to give me the total miles traveled for that vehicle in the current log.

I appreciate any help with this. I am just tired of banging my head on the wall over it.

Thanks,
~tink

[Richard Buttrey]

Hi,

Upload your workbook. We'll need to see the layout to be able to advise further. Manually add some typical results that you expect to see so that we know the end destination.

[Chaintinker]

I am looking to fill in the Miles Column with the difference in odometer readings for each vehicles.

Sorry I forgot to fill in an example.

Thanks

[ChemistB]

I used the following formula

=LOOKUP(2,1/($E$2:$E$13=J2),$G$2:$G$13)-INDEX($G$2:$G$13,MATCH(J2,$E$2:$E$13,0))

It subtracts the first entry (assuming they are in chronological order) from the last entry. Two things.
1. your data doesn't make sense because the odometer goes down sometimes. Not good
2. You're including your first fill up in the total gallons which would be gas used before the difference in miles.
for example, at 100 miles on odometer, truck fills up with 34 gallons, then at 500 miles, fills up with 30 gallons, they traveled 400 miles on 30 gallons, not 400 miles on 64 gallons. Make sense?

[Chaintinker]

Thank you for your formula, I am wondering if you could explain how it works a little bit so that I can tweak it to fit into the whole spreadsheet. I would also like to understand it so that i can use aspects of it in the future.

What I posted was a sample of the sheet that gave the applicable fields (I filled in random entries in some areas, and that is why the odometers jump around. Sorry)
As for your second issue. This is going to be a running total throughout a year. I can look back to the previous sheet to reference the older data.

Thank you for your help,
~tink
(This would have been a private message, but i don't have 5 posts yet to grant me the PM privileges)

[ChemistB]

Okay, Looking at the first part
LOOKUP(2,1/($E$2:$E$13=J2),$G$2:$G$13)

LOOKUP looks for values until it finds a value equal to the value. If it doesn't find one, it'll go to the last value it found.
We're looking for 2
It's shifting through 1/($E$2:$E$13=J2). If the value in E equals the value in J2, this will simplify to 1.
If the value in E does not equal J2 the denominator becomes false or 0 which (dividing by 0) results in an error
The Lookup ignores error so keeps looking until it finds a 2 (which is never) and then it goes back and matches the last 1 (or matching value)
So if that's in the 5th row of E2:E13, then it returns what's in the 5th row of G2:G13.

Part 2: INDEX($G$2:$G$13,MATCH(J2,$E$2:$E$13,0))
This is a typical index formula. We're returning from G2:G13 and Match(J2,$E$2:$E$13,0) looks for an exact match in col E for J2 (the 0 tells excel to look for an exact match).
The match function stops once it finds a match so we will return the first odometer reading for the vehicle in J2.
So now we have the first and last odometer reading. Subtraction yields miles driven.
Any questions?

[Chaintinker]

Thank you, It looks like it is working great now.

Thanks again.

[Chaintinker]

I know I marked this as solved, but is there a way to modify the formula so that when the LOOKUP does not find its target, it will display either a blank cell or a "0"? Currently it gives me a "#N/A".

Thanks again.

[shg]

=iferror(index($g$2:$g$13, match(j2, $e$2:$e$13, 0)), "")