I have a workbook that a user will log in various vehicle expenses.
One of the features is that it will calculate the MPG for each gas
purchase if the tank is filled up. This is simple, but I also want it
to calculate the MPG to including times when the user does NOT fill
the tank. This is done by adding the gallons used since the last fill
up thru the most recent fill up. It will divide that number by the
total miles driven during this period. The data is collected via a
userform, and the data is pasted to another sheet within the same
workbook. Here’s a example:

We’ll assume the headers Date Fillup ? Gal and OD Reading is in
cells A1-D1. The last line (the 2/15/10 entry) is in cells A5-D5.


Date Fillup ? Gal OD reading
1/1/2010 Y 14.25 24500
1/15/2010 N 15.25
2/1/2010 N 13.25
2/15/2010 Y 13.58 25500

Since a Y was entered on the last purchase, and it was N in the entry
prior, the calculation would be
(15.25+13.25+13.58) / (25500-24500) = 17.8 MPG from 1/15/10 thru the
2/15/10 fill up.

What I want to happen is when a Y is recognized on the current gas
purchase, it will:
1) do the simple calculation if the previous gas purchase was Y. That
was the case in the 1/1/10 entry.
2) It will calculate nothing if the User indicates N in the current
purchase.
3) If the user enters Y, and the previous entry was a N, then it will
add the gallons used since the last fill up, and divide that number by
the difference between the current OD reading, and the last reading
with a Y. This was the scenario in the example above that resulted in
the 17.8 MPG.

I hope I laid this out clear enough, and gave enough information. If
someone can help me with the coding to perform this task, I would
appreciate it.

As always, thanks for your help.

jeff