How to take event intervals and use Excel to build a timeline?

**MisterW** · 08-14-2012, 01:02 PM

Found this site while trying to search for an answer, but I'm not even entirely sure what to search for. I'm building an operations & maintenance model to predict major maintenance intervals based on how a machine is being used. I'll make up an analogy to illustrate.

Let's say you have a bicycle, and you have to service it as follows:
Every 3 rides, grease the chain
Every 10 rides, overhaul the chain (resets the "grease" interval)
Every 32 rides, replace the chain
<repeat>

I want Excel to list the timeline of events going forward. So if the bike has been ridden 11 times already (and thus an "overhaul" was performed at 10), I want the following:

Column A____Column B (aka, how many more rides from today)
grease______2
grease______5
grease______8
overhaul_____9
grease______12
grease______15
grease______18
overhaul_____19
replace______21

Thank you!

**NickyC** · 08-14-2012, 11:07 PM

thanks for one of most clearly defined statements of a puzzle I've seen!

one of the cleverer gurus on this site can probably do this with formulas, but I resorted to a macro

this should list a replacement schedule over 1000 days, starting in Cell A6:

Please Login or Register  to view this content.

**MisterW** · 08-15-2012, 05:12 PM

That is really cool, one of my goals is to learn how to write and read macros, and that helps a lot. I right clicked on a tab in a new workbook, clicked "view code", pasted your code and clicked "run" and the output looks perfect for the bicycle example! Thanks for your help :-).

Since this maintenance timeline calculation is only one part of a larger model and is going to be used by a data table to do a sensitivity analysis, a formula would be ideal. I'm circling back to this project and nearing in on a formula that works for this, so I'll plan to post an update soon.

**MisterW** · 08-15-2012, 05:53 PM

Here's what I came up with, I'm testing the formulas with a bunch of inputs to make sure they act correctly. Please let me know if you seen an error.

bicycle example.JPG

formula in A17 (copied down) is =IF(ISNUMBER(B17),IF(B17=$C$14,"replace",IF(OR(MOD(B17,$C$8)-$C$9=0,MOD(B17,$C$8)=0),"overhaul","grease")),"")

formula in B17 is =MIN(C4,C9,C14)
formula in B 18 (copied down) is =IF(B17<$C$14,IF(B17<$C$9,MIN(CEILING(B17+0.001,$C$3)+MOD(B17,$C$3),$C$9,$C$14),MIN(CEILING(B17+0.001,$C$3)+MOD(B17,$C$3),CEILING(B17-$C$9+0.001,$C$8)+$C$9,$C$14)),"")