Collecting data for MPG (miles per gallon) calculation

[jeff]

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

 

[Wouter HM]

Hi Jeff,

You van try something like this

I asume your details start at row 2

Add 3 columns
In these column the functione starts from row 3

1:
Titled: Summarized Galons
funcion: =IF(B2="Y",C3,C3+E2)

2:
Titled: Forwardded Reading
Function: =IF(B3="Y",D3,F2)

3:
Titles: MPG
Function: =IF(B3="Y",(F3-F2)/E3,"")

THurther more I think there is an error in your calculation.
I can not reproduce the result of 17.8 MPG

HTH,

Wouter

 

[Paul C]

First off, you have your formula inverted the MPG calc should be
(25500-24500)/(15.25+13.25+13.58)= 23.8 MPG

The key to this calculation is finding and using the last fillup date

Date Fillup Gal OD Last Fillup Gal Miles MPG
1/1/2010 Y 14.25 24500
1/15/2010 N 15.25 1/1/2010
2/1/2010 N 13.25 1/1/2010
2/15/2010 Y 13.58 25500 1/1/2010 42.08 1000 23.8
3/1/2010 N 14.25 2/15/2010
3/15/2010 Y 14.25 26200 2/15/2010 28.5 700 24.6
4/1/2010 Y 13.58 26500 3/15/2010 13.58 300 22.1

For explanation I added four columns with formulas starting in row 3 (row 1
being the heading and row 2 the start point.

For the last fillup date in e2 =LOOKUP(2,1/($B$2:$B2="Y"),$A$2:$A2)

The Gal Used in
F2=IF(B3="Y",SUMPRODUCT(--($A$2:$A3>$E3),--($A$2:$A3<=$A3),$C$2:$C3),"")

The miles driven in G2 =IF(B3="Y",$D3-VLOOKUP($E3,$A$2:$D3,4),"")

The MPG in H2=IF(F3<>"",G3/F3,"")

This can be condensed into one monster formula
=IF(B3="Y",($D3-VLOOKUP($E3,$A$2:$D3,4))/SUMPRODUCT(--($A$2:$A3>=$E3),--($A$2:$A3<$A3),$C$2:$C3),"")

The absolute and relative references are key to copying this down. That is
the $A$2:$A3 ties the formula to the start $A$2 and $A3 will expand the
formula range to match the row.

 

[JLatham]

I had to modify the formula you show for G2 somewhat, and put it starting at
G3. So my formulas, based on yours, that give good results are:

In E2: =LOOKUP(2,1/($B$2:$B2="Y"),$A$2:$A2)
In F2: =IF(B2="Y",SUMPRODUCT(--($A$2:$A2>$E2),--($A$2:$A2<=$A2),$C$2:$C2),"")

In G2: no formula
in G3: =IF(B3="Y",$D3-VLOOKUP($E2,$A$2:$D3,4),"")
In H3: =IF(F3<>"",G3/F3,"")

Very nice solution you came up without resorting to VBA.

 

[Andrew]

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

In the event that the user does not fill the tank, how do you
determine how many gallons were used since the last fill up?