Need Spread Sheet Solution Help

D

dcollis

Ok, here is a sample of the data i have:

Component No Interval(hr)
Labour Hours
Electrical Management System 1 250 2
Engine Repairs 1 250 2
Exhaust Pipe 1 250 1.5
Fan Drive 1 38000 16
Final Drive Pump 1 15000 3
Fire Supression 1 9000 5
Flush Radiator 1 9500 3
Frame 1 250 2
Fuel System 1 9500 12


What I want to create is like a history, eg between the interval of 0
to 1000hrs there is "x" labour hours required. this need to go out for
45,000hrs, so these events reoccur.

can anyone think of a way to set this out efficently?

Thanks
 
M

Martin Fishlock

Hi:

You could try the sumproduct function on the bounds as in a table:
[Col e] [Col f] [Col g]
start finish hrs
0 1000
=sumproduct(--(($c$2:$c$10)>e2),--(($c$2:$c$10)<=f2),$d$2:$d$10)
1000 2000
=sumproduct(--(($c$2:$c$10)>e3),--(($c$2:$c$10)<=f3),$d$2:$d$10)
2000 3000
=sumproduct(--(($c$2:$c$10)>e4),--(($c$2:$c$10)<=f4),$d$2:$d$10)

.....
 
D

dcollis

Sumproduct is what i would use, but this does not handle the re-
occurance of events.

eg, the engine repairs happen at an interval of 250hrs - so it happens
at 250, 500, 750, 1000 etc etc until 45000

Cheers
 
M

Martin Fishlock

OK got what you are trying to do and I think that a straight table is the
best and depending on the number of components if it is vertical or
horizontal will just depend on your personal preferences.
 
Top