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

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