How to track cumulative total with changing values??

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

We need to track our cumulative total operating hours of a piece of
equipment. Unfortunately, maintenance often requires us to change the
module that tracks the hours and sends them to the display. And the
replacement module can be a used one, so the hours don't always reset
to zero, but come up at whatever hours were previously stored in the
module.

Say we get the equipment with 10 hours on it, but we start counting at
zero hours. At 50 hours, we change the module, and the hours are now
shown as 1. At 16 hours displayed, we change the module again, and
hours are now displayed as 100. So my hours are:

Displayed Actual
10 0
50 40
1 40
16 55
100 55

How would you set this up to track the total cumulative hours through
all these changes?

Ed
 
I forgot the biggest headache in this. We not only track the
operating hours of the equipment, but other items on the equipment.
These do not always match nicely with the hours module replacement!

So I have an attachement installed during the life of the first module
at 20 hours displayed. Another is installed during the life of the
second module at 10 hours displayed. We get a hand-written form at
the end of the day with the ending hours for the whole vehicle. We do
have a master spreadsheet for the equipment and all installed items
that updates biweekly by entering the ending parameters of the
vehicle.

Is there a quick and easy way to simply track the changes in operating
hours as displayed by the vehicle's hour meter, and have it
automatically figure into the calculations for the operating hours of
all the individual attachments?

Ed
 
I believe you definitely need to add another column which indicates
when you've replaced the unit. Unless the replacement unit always has
less hours than the one it replaced. But the last row in your example
shows that this isn't true. So with the new column, you can use the
following formula in the "Actual" column, and copy it down:

=IF(B2="Yes",SUM(C1),C1+A2-A1)

Displayed Changed Actual
10 Yes 0
50 40
1 Yes 40
16 55
100 Yes 55
 
Back
Top