I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a

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

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.

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

"Dave" wrote in message
...
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.

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


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a

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

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. Valko" wrote in message
...
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................??...


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a

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

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((B 2-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.

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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. Valko" wrote in message
...
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................??...


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a

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

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" wrote in message
...
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((B 2-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.

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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. Valko" wrote in message
...
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................??...


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a

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

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!


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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" wrote in message
...
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((B 2-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.

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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. Valko" wrote in message
...
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................??...


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I have a worksheet used for calculating vehicle fuel economy
statistics. The columns a

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