MPG Calculations

[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

 

[Dave]

Hi Terry,
For mpg calculations, you don't really need to consider whether the tank was
full when you started, unless you have a very small sample of data.
All you really need is total miles divided by total gallons. Each time you
put petrol in, whether you fill it or not, just record the gallons and the
miles. The more miles and gallons you record, the less it will matter what
state of fill the tank was in when you began.
Regards - Dave.

 

[T. Valko]

Based on this data tell me what results you would expect:

......Miles.....Gals.....Filled = Y.....MPG
......1000.......12..................................
......1367.......11.........Y......................
......1623.......10..................................
......1972.......14.........Y................??...
......2200.......13..................................
......2500.......10..................................
......2833.......12.........Y................??...

 

[Terry Bennett]

Biff

The first reading would be (1972-1367)/(10+14) = 25.21.

The second would be (2833 - 1972)/(13+10+12) = 24.6.

Thanks.

Terry

 

[Terry Bennett]

Dave

The vehicles in question are buses with large tanks that do relatively small
mileages. I also need to monitor the MPG per trip rather than an overall
figure.

Terry

 

[T. Valko]

Ok, based on that sample table I posted...

A11 = column headers

Enter this formula in E2 and copy down as needed:

=IF(COUNTIF(D$22,"Y")<=1,"",IF(D2="","",ROUND((B2-LOOKUP(2,1/(D$11="Y"),B$1:B1))/SUM(C2:INDEX(C$1:C2,MATCH("zzz",D$11)+1)),2)))

You'll notice that there are some references to the header row. This is
intentional.

 

[Terry Bennett]

Biff

That seems to have cracked it!

Just for my enlightenment, what is the reference to "zzz"? Is that some
form of wildcard?

Many thanks!

Terry

 

[T. Valko]

what is the reference to "zzz"?

That's used to find the last instance of "Y" in column D.

....MATCH("zzz",D$11)

How it works...

MATCH has 3 arguments to it. The 3rd argument tells it what type of search
to use for the lookup value. If the 3rd argument is not specified or omitted
(as I have done) it defaults to a match type of 1 meaning the lookup array
is sorted and if an exact match is not found it will match the closest value
that is less than the lookup value.

Using the match type 1, if the lookup value is greater than any value in the
lookup array the match will be made at the *last* value in the lookup array
that is less than the lookup value. This works for both numeric values and
TEXT values.

Think of the alphabet. A, B, C,...X, Y, Z

Our lookup value is "zzz". The only entries in column D will be the letter
"Y". The lookup value "zzz" is greater than "Y" so the match is made at the
*last* "Y" in the lookup array. The *last* "Y" is found depending on how far
down the column the formula is copied to.

This might seem kind of confusing at first but if you read it a few times or
try some tests it'll (eventually) make sense!

 

[Terry Bennett]

Biff

Many thanks for the explanation. I get your drift although I need to
unscramble the larger formula to make sense of it all bit by bit.

I always find my excursions to this forum to be humbling experiences.
Although I understand most aspects of Excel pretty well and am more
proficient at it that 99% of the people I work with, whenever I come on here
99% of the participants are better at it than I am!!! Good for coming back
down to earth I guess!

Terry