T
Terry Bennett
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns are:
A - Date
B - Mileometer reading
C - Amount of fuel added
D - Whether tank full or not ("Y" if full / blank if not)
In column E I want to display the Miles Per Gallon (MPG) based upon the most
recent fill-up. Hence, if the tank was filled, I need to devise a formula
that refers back to when it was last full (ie; when column C last had a "Y"
entered) and then subtracts today's mileage from that in column B when the
tank was last full. If the tank was not full then column E remains blank
until next time it is full.
I can only think of using VLOOKUP for this but not quite sure how as there
will be many instances of "Y" in column C - I just need the most recent.
Any ideas?
Thanks.
Terry Bennett
The columns are:
A - Date
B - Mileometer reading
C - Amount of fuel added
D - Whether tank full or not ("Y" if full / blank if not)
In column E I want to display the Miles Per Gallon (MPG) based upon the most
recent fill-up. Hence, if the tank was filled, I need to devise a formula
that refers back to when it was last full (ie; when column C last had a "Y"
entered) and then subtracts today's mileage from that in column B when the
tank was last full. If the tank was not full then column E remains blank
until next time it is full.
I can only think of using VLOOKUP for this but not quite sure how as there
will be many instances of "Y" in column C - I just need the most recent.
Any ideas?
Thanks.
Terry Bennett