Vehicle Utilisation Chart (Hell Stuck)

[Steven North]

I need some help, I'm currently reporting on 35+ vehicles
and trying to chart quartery utilisation. I have the
entire vehicle's utilisation in an excel sheet but I'm
trying to get it to generate the utilisation chart so I
can copy and paste into word. Except I'm having troubles.

It has to be compared against the allowed kms per vehicle.
So it's basically trendlines. This is what it should be
doing over the period of time from quarter 1 onwards
(Delivery Date) to the end date (based on term eg.
24months 40000kms) so 01.01.2000 on 24 months is
01.01.2002 with 1666.67 kms allowable per month.

With these vehicles being pool vehicle's I'm trying to
chart without having to do any tedious and intense manual
data manipulation to break the data down and provide a
general overview of what the vehicle is doing compared to
it's lease.

The data involved is....

Odometer Reading
Date of Reading
Term in Months
Term in Kilometers
Delivery Date
Disposal Date (calculated formula)
Formula :: (Delivery Date + Term in Months*365/12)

The data is in an excel sheet and looks something like..

Date odo
04/11/01 732
14/11/01 1198
25/11/01 1692
08/01/02 3467
etc.etc.

As these are based on 35+ vehicles

1 chart per vehicle. I'm stuck on generating these graphs
without having to include formulas, copy and paste the
results as values, sort, copy and paste etc. etc.

Is there a much easier way to simply the amount of work?
Or customise Pivot Charts?

Any help is appreciative

 

[Jon Peltier]

Steven -

If you have vehicle number, date, and odometer reading columns in your
data range, make a pivot table with Date in the row field, Vehicle
Number in the column field, and Odometer (sum or average doesn't matter,
if each vehicle has at most one reading each day). You get one column
for each vehicle's mileage, which can be plotted vs. date.

I can't check out pivot charts on this machine (we're still on office 97
at work). ISTR that you can choose in a dropdown which series to show or
hide. In any case, you can hide the series in the pivot table itself,
which would hide it in the chart.

- Jon

 

[Steven North]

I managed to get the sum of odometer reading differences
to chart for one vehicle. But when I get to two, it
doesn't want to group the data's in the row field.
Hmmm I'll try the ODO & Number in column field as I used
the Odo in the data field.

What I've also got is how to chart the monthly allowable
kms from a given date and no. of months to be used as a
comparison guide in the chart.

 

[Steven North]

Ok, It didn't quite work as what I hoped for.
I managed to end up creating a template for each vehicle
and copied the source data into it, and modify the
multiple tables I required to chart.

I didn't bother charting the vehicle details, but what I
did was chart Date in row field, diff reading in column.
The diff was calculated by one odo take the other, had to
delete some rows if the conditional format didn't like the
end results as a 0 odo reading throws the entire chart out
of whack.

With the Allowed monthly Kms, I had to create a seperate
table using the delivery date as the series start date and
created a macro to fill until the 30/jan/04. (All this is
compiled in a pivot table) Grouped the Date by Months,
Quarter & Year) to make the vehicle usage chart over the
life of the vehicle.