ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Mileage and MPG for Fleet Calculation (https://www.excelbanter.com/excel-discussion-misc-queries/155151-mileage-mpg-fleet-calculation.html)

Scott Campbell[_2_]

Mileage and MPG for Fleet Calculation
 
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



Ron Coderre

Mileage and MPG for Fleet Calculation
 
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


"Scott Campbell" wrote:

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



Bob Phillips

Mileage and MPG for Fleet Calculation
 
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" wrote in ...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 upfor that particular truck. Any suggestions? Thanks in advance... Scott



All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com