CALCULATING SUMS

[markstro]

I have a collection of data in a spreadsheet that is 50,000 + lines
long for truck mileage sorted by vehicle number.
The column left of the truck number is the odometer reading when it was
fueled.
I need a formula or function that will find the first entry and last
entry for each vehicle and subtract the last mileage from the first to
give me total mileage for each vehicle.
The total lines for each vehicle varies from vehicle to vehicle.
Hope I gave enough info to figure this out.
Thanks, Mark

 

[Guest]

Maybe a pivot table?:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TruckNUmber field here

DATA:
Drag the Odometer field here
dbl-click it and set it to Min

Drag the Odometer field here, again
dbl-click it and set it to Max

Click [OK]
Select where you want the Pivot Table...and click the [Finish] button

That will list each truck and the Min and Max odometer readings...but, in
the wrong configuration (stacked instead of side by side).

Click and hold on the DATA heading
Drag it on top of the Total heading and release.

Now the Min and Max are side by side

All that's left to do is put formulas to the right that subtract the Min
from the Max.
If Excel keeps creating the GetPivotData function when you attempt to build
the formula....you can disable that feature by following the instructions at
Debra Dalgleish's website:
http://www.contextures.com/xlPivot06.html#GetPivotData

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

XL2002, WinXP

 

[Biff]

Hi!

Try this:

Column A = odometer reading
Column B = vehicle number

=LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))

Biff

 

[Biff]

subtract the last mileage from the first

Hmmm.....

My formula does the opposite!

Just flip it around so that it's:

=INDEX(........)-LOOKUP(........)

Biff

 

[markstro]

I am trying to work out the pivot table suggestion, however, I tried
yours as well and have a few questions.
I get an #N/A when I type your formula as shown.
Vehicle number is in column E titled TRUCK, odometer is column D
labeled ODO
What does the 2,1/ mean at the beginning of your formula?
What should the result look like when I get the bugs out of the
formula?
Thanks for your help

 

[dbahooker]

you should find some junior database developer to help you with this.

Excel doesn't work for managing DATA. a DATABASE would be a better
choice.

I would put a posting on Craigslist to look for an Access Developer and
you'll probably get the report you need in just a few minutes.


-Aaron

 

[markstro]

The pivot table worked out just fine, thanks for the tip though.

 

[markstro]

Thanks Ron, it worked just fine, you even got me over a hurdle
regarding pivot tables I have been struggling with (self teaching).
Thanks again, Mark

Maybe a pivot table?:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TruckNUmber field here

DATA:
Drag the Odometer field here
dbl-click it and set it to Min

Drag the Odometer field here, again
dbl-click it and set it to Max

Click [OK]
Select where you want the Pivot Table...and click the [Finish] button

That will list each truck and the Min and Max odometer readings...but, in
the wrong configuration (stacked instead of side by side).

Click and hold on the DATA heading
Drag it on top of the Total heading and release.

Now the Min and Max are side by side

All that's left to do is put formulas to the right that subtract the Min
from the Max.
If Excel keeps creating the GetPivotData function when you attempt to build
the formula....you can disable that feature by following the instructions at
Debra Dalgleish's website:
http://www.contextures.com/xlPivot06.html#GetPivotData

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

XL2002, WinXP

I have a collection of data in a spreadsheet that is 50,000 + lines
long for truck mileage sorted by vehicle number.
The column left of the truck number is the odometer reading when it was
fueled.
I need a formula or function that will find the first entry and last
entry for each vehicle and subtract the last mileage from the first to
give me total mileage for each vehicle.
The total lines for each vehicle varies from vehicle to vehicle.
Hope I gave enough info to figure this out.
Thanks, Mark