MPG Calculations


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
 
Ad

Advertisements

D

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

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................??...
 
T

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
 
T

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

T. Valko

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

A1:D1 = column headers

Enter this formula in E2 and copy down as needed:

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

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

Advertisements

T

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

T. Valko

what is the reference to "zzz"?

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

....MATCH("zzz",D$1:D1)

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!
 
Ad

Advertisements

T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top