Mileage and MPG for Fleet Calculation

[Guest]

I have a table in Excel that looks something like this:

Date Truck Name Gallons Price
7/31 Truck 1 12.345 2.869
7/31 Truck 2 11.255 2.869
7/31 Truck 3 10.587 2.869
8/3 Truck 1 11.256 2.879
8/3 Truck 2 12.854 2.859
8/4 Truck 1 10.857 2.879
8/4 Truck 3 17.588 2.879

I need to add a 5th column called Previous Fillup.
In this new column I need it to figure out the date of the last fill up for
that particular truck.

Any suggestions?
Thanks in advance...

Scott

 

[Guest]

Try this:

E1: PrevDate

E2: =INDEX($A$1:$A2,LARGE(INDEX(($B$1:$B1=B2)*ROW($B$1:$B1),0),1))
Copy E2 down as far as you need

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Bob Phillips]

Add this to E3 and copy down

=IF(MAX(IF($B$2:B2=B3,ROW($B$2:B2)))>0,INDEX($C$2:$C$8,MAX(IF($B$2:B2=B3,ROW($B$2:B2)))-MIN(ROW($B$2:B2))+1),"")which is an array formula, it should be committed with Ctrl-Shift-Enter, notjust Enter.Excel will automatically enclose the formula in braces (curly brackets), donot try to do this manually.When editing the formula, it must again be array-entered.-----HTHBob(there's no email, no snail mail, but somewhere should be gmail in my addy)"Scott Campbell" <[email protected]> wrote in messagehave a table in Excel that looks something like this:>> Date Truck Name Gallons Price> 7/31 Truck 1 12.345 2.869> 7/31 Truck 2 11.255 2.869> 7/31 Truck 3 10.587 2.869> 8/3 Truck 1 11.256 2.879> 8/3 Truck 2 12.854 2.859> 8/4 Truck 1 10.857 2.879> 8/4 Truck 3 17.588 2.879>> I need to add a 5th column called Previous Fillup.> In this new column I need it to figure out the date of the last fill upfor> that particular truck.>> Any suggestions?> Thanks in advance...>> Scott>>