Charts: How to create a Square Wave?

  • Hi Peeps! Been out of the country for a while so I’ve not monitored the site for a while. Now I’m back at work and have a problem on my first day!!


    It’s easiest to explain if you look at the attachment. This is a plot of system availability over time, where Preventive Maintenance (PM) is carried out at set intervals. It should be a sort of square wave with sloping or curved tops, the curvature of the top being varied by the value of “Beta”.


    The value (t-x) is used to reset the elapsed time to zero whenever PM is done. The formula is =MOD(Time, PM Interval).


    The column “Plotted Availability” is used to create ‘dropouts’ in the chart for the duration of the PM, which is set by the value of ‘MTTR’


    In the “Parameters” block, Yellow cells are user-entered values, Blue cells are calculated values.


    The plot doesn’t quite do it for me, because it zigs down to zero at the PM point, then zags back up, MTTR hours later. What I need is for it to drop instantly to zero at the PM Point, stay at zero for MTTR hours, then rise instantly to the value of A at PM+MTTR hours. I realise this will require 4 plotting points for each PM instead of the current 3, but I’m at a loss as to how to implement this – any clues much appreciated.

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • Try the attached. I added an extra row after every 10th calculation and changed a few formulas. I shaded the ones for the first set of changes. I also changed the formulas in Column C to If staements, since for some strange reason, Excel wasn't corectly updating one or tow of them whan I changed parameters. It should have, but didn't. Also the lines don't drop precisely straight for me, even though the x values are identical. It must be Excel is misbehaving a tad for me today.

  • Hi Relman,


    Try the attached.


    It uses a dummy set of data to mask the dips. These can not be completely removed from the chart because you are using formula to create the data.
    The dummy data series also uses Y error bars to give the clean vertical drops.


    By formating the dummy series the same as the plot area the dips are masked.
    By formating the error bars the same as the actual data series the vertical drops blend in.


    The dummy data series is feed values by some simple formula.


    Hope this helps.


    Cheers
    Andy

  • Thanks for that Derk :congrats:
    I hoped that either you or Andy Pope would pick up on this!
    It looks about as close as we can get for now, and if you didn't have such a scruffy-lookin' beard I'd give ya a big kiss! :puke:


    FYI, It bends on my Excel as well :wink2:


    FYI2, I've cross-posted this on Tek-Tips, and "SkipVought" is working on it right now, so maybe he'll come up with a way to cure "The Bends" :biggrin:

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • Damn! must refresh my browser before posting!
    Thanks Andy, I'll look and let you know Pronto!

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • Andy & Derk = "Da Men"


    But... neither solution is affected by the length of time taken to do the PM, ie the value of MTTR. :(


    Parameters MART and ADLT are Mean Active Repair Time = "Spanner in hand time", and Additional Logistic Delay Time = Transit Time+Waiting for Spares+Testing, etc.


    So if you set MART to 12 and ADLT to 100, PM to 300, the first PM drop should occur at 300, stay at zero, then zoom up again at 412. The next drop should occur at 712 to 824, and so on....


    Any hope of doing that?

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • Derk: Yep, I'm sure. Yours works gorgeously as far as the dropouts go, but I need the time at zero to expand/contract along with the value of MTTR calculated in B8. Both your solution and Andy's shift the plot to the right by MTTR units, but I think that's MY fault for not calculating it properly!


    Anyhoo, both of your answers have helped me a great deal, and inspired me to try another tack. I'll have a rethink and post back ASAP.

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!