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.
"Paul C" wrote:
> 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.
> --
> If this helps, please remember to click yes.
>
>
> "jeff" wrote:
>
> > 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
> > .
> >
Similar Threads
