XL2K: How can I Chart a Square Wave? 2

[MeGustaXL]

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!!

The table below is an extract from the data I'm using to generate 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 “Plot” is used to create ‘dropouts’ in the chart for the duration of the PM, which is set by the value of ‘MTTR’

The resulting 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.

Parameters
MTBF 400.000
Beta 1.000
Eta 400.000 Average Ao
Ao 0.943 0.838
MART 24.000 Minimum
ADLT 0.000 0.753
MTTR 24.000
PM Interval (x) 100.000

Time (t) (t-x) Elapsed Instant Plot
0.000 24.000 1.000
1.000 1.000 25.000 0.998 0.941
10.000 10.000 34.000 0.975 0.920
20.000 20.000 44.000 0.951 0.897
30.000 30.000 54.000 0.928 0.875
40.000 40.000 64.000 0.905 0.854
50.000 50.000 74.000 0.882 0.833
60.000 60.000 84.000 0.861 0.812
70.000 70.000 94.000 0.839 0.792
80.000 80.000 104.000 0.819 0.772
90.000 90.000 114.000 0.799 0.753
100.000 0.000 124.000 1.000
110.000 10.000 134.000 0.975 0.920
120.000 20.000 144.000 0.951 0.897
130.000 30.000 154.000 0.928 0.875
140.000 40.000 164.000 0.905 0.854

If it helps I can e-mail the actual spreadsheet to anyone who thinks they may be able to do this.

Fingers X'd


Chris

Varium et mutabile semper Excel

 

[SkipVought]

Chris,

Send me your workbook @ skip>metzger@lmco.com

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Basicall to get 2 square waves, with amplitudes of 5 & 10...

Time (t)  Amplitude
0         5
10        5
10        0
20        0
20       10
30       10
30        0
30        0

Then it's just a matter of generating the interval/amplitude data points for the top.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I think that your "square wave" source data might look something like this -- Just added and second Time (t) value for each begin/end transition point and corresponding 0 Plot value...

Time (t) Plot

1 0
1 0.941
10 0.92
20 0.897
30 0.875
40 0.854
50 0.833
60 0.812
70 0.792
80 0.772
90 0.753
90 0
100
110 0
110 0.92
120 0.897
130 0.875
140 0.854
140 0


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Thanks for those suggestions Skip, I'll try them out and let you know.

BTW, I've cross-posted this on Ozgrid, and had a couple of suggestions from Derk and Andy Pope which I'm evaluating now.

In the meantime, I've sent you my worksheet, and Derk's to TheOfficeExperts.com 'cos it bounced from the other address

It's actually a bit more complicated than at first sight, because the mark/space ratio is variable, depending on the value of MTTR.

In the example above, the first PM would occur at 100, the plot stays at zero for 24 (MTTR) then goes up again at 124.
The next PM doesn't occur at 200, but at 224 because of the time taken to do the first one...if you see what I mean!

FYI, Back on Ozgrid, Andy Pope has sent a solution using negative error bars and a hidden series which give a nice effect, but doesn't address the variable MTTR value.

I'll keep chipping away at it

Chris

Varium et mutabile semper Excel

 

[SkipVought]

So you question is NOT ONLY how to plot a "square wave" BUT ALSO, how to generate the data points based on your parameters?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Yup, I guess so...is that a problem?

BTW, did you get my mail? (any of them).



Chris

Varium et mutabile semper Excel

 

[Chattin]

Might be completely off track here but is this any use?

http://www.stfx.ca/people/bliengme/ExcelTips/Fourier_Sum.htm

HTH

 

[SkipVought]

Got no eMail.

Questions/clarification statements...

1. you have a time interval of 10 hrs that begins with 1.

2. Beta is a negative value (slope is negative), TRUE?

3. How is the Plot point calculated based on Time, Beta and other given parameters?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

try skip.metzger@lmco.com

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I have a square wave. Will send tomorrow



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Hi Skip and Chattin, and thanks for all your input Guys, it's much appreciated.

Answers/Clarification (I hope):

1. I have a time interval of 1/10th of the PM Interval entered by the User. The first point is 1, because the other calculations use logs, which bomb out if zero is used.

2. Beta IS the slope, but is always positive, and preferably >=1. It's one of the parameters in the WEIBULL function, and corresponds to Failure Rate. If it's less than 1, FR is decreasing, ie the equipment is getting more reliable, analogous to a "running in" period for a new engine. If B=1, FR is constant - failures occur randomly over time. If B>1, FR is increasing - reliability gets worse as the equipment ages, at a rate proportional to B.

3. The plot points at time (t) are calculated using the formula:

        Y=1-(EXP(-(t/Eta)^Beta))

Chattin: Thanks for that steer to Bernard Liengme's site; it's not what I need at present, but it helps on another problem ==>* for you!

Skip: I've e-mailed again, hope it gets thru' this time!

I think I've worked out a way to do it: I'll have a tinker this morning and post back ASAP.

Later, Guys!


Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Skip, I got your e-mail, thanks. Still doesn't change the downtime, tho'

I've pinged you with my kludgy answer in the hopes you can wave the "Wand of Skip" over it

In the meantime, I've STAR'd you for your help so far - Cheers Buddy!



Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Thanks for the improved solution Skip - I've STAR'd you again for that.

If ever I want to Confoundicate and Baffle-ise my Users, I'll just throw in those formulas from columns C and D:

=IF(OR($B13=0,$B13=1),0,IF($B13=PlotPoints-2,D12,IF($B13=PlotPoints-1,D12+MTTR,D12+Time_Interval)))[code]

Whaaaat!!???

Anyways, it all works perfectously now, so thanks again, Pal. [2thumbsup]

Chris

[b][i]Varium et mutabile semper Excel[/i][/b]