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