Excel frequencies formulation

[AndreMeyer]

Hi Guys

Wonder if you might be able to give me a hand. I'm trying to figure out a whether there is a formula in excel that can help me with some interval issues I'm having.

So basically we want to record hourly services on our engines (trucks)

The services are as follows:

1) A Service - Occurring every 125 hours
2) B Service - Occurring every 250 hours
3) C Service - Occurring every 500 hours
4) D Service - Occurring every 750 hours
5) E Service - Occurring every 1000 hours, and
6) F Service - Occurring every 2000 hours.

Now, they way it works is if, for instance, the month of August had 500 hours then the service schedule would be:

125 hour service - occurring at 125 hours
250 hours service - occurring at 250 hours
125 hour service - occurring at 375 hours, and
500 hour service - occurring at 500 hours.

So that would be two (125 service), one (250 service) and one (500 service) for the month of August.

At the moment were entering this data manually into our excel cells and what I'm trying to figure out is whether there is a formula that can calculate this for me automatically if I just insert the number of hours for a given month. So if I change 500 hours for August to 1000 hours
the formula would change the fields to:

125 hour service - occurring at 125 hours
250 hours service - occurring at 250 hours
125 hour service - occurring at 375 hours
500 hour service - occurring at 500 hours
750 hour service - occurring at 750 hours, and
1000 hour service - occurring at 1000 hours.

Thank you all in advance for your assistance. Please let me know if you require any further details.

Regards

André

[bsmill]

André
I looked at your request and to be honest it made no sense to me. Just by knowing that you have 500 hrs. in the month of Aug. to me that would mean ALL your Units is due some type of service, because the min. Service hrs is 125. I have worked all my life with maintaining trucks and other equipment and a system similar to the one below works well but only with prompt and proper information updates. Current hours can be obtained through many different ways, like fuel tickets, end of day reports, Morning reports, trip sheets, lease forms and others.
OR start over by changing = Current Hrs. to Start Hrs. AFTER the "E" Service is completed. Column "D" will calculate the difference between the two.
Col A, Unit # "B", Start Hrs. "C", Current Hrs. "D", (Hrs. used to calc. service intervals) "E", Service "F", Service Completed "G", Service Date "H", Service Change "I2" to "I10" Interval Hours (A - F) "J2" to "J10", (A,B,A2,C,A3,D,A4,E,F).
=VLOOKUP($D2,$I$2:$J$10,2) Enter into Column "E" row of first Unit #
=($C2-$B2) Enter into Column "D" same row to have the differences in hours.
=IF($E2=$F2,"","Service Due") Enter into Column "H" in row of first unit #

To change Font colors highlite column right click, go to format cells=Font tab change font color click OK.
When you have the equations in the proper cells “Double click the Fill Handle” OR grab the fill handle and drag it to the bottom of your Dataset.

Rows “I” and “J” are the reference columns. If you have to move them just highlite both columns then move them as far right as needed. Make sure that the equation changes to the proper rows and columns. Then Save.

Bsmill