WEIBULL formula to get probability rate

F

Fin Fang Foom

Hi everyone,

Hi everyone,



Say if I schedule a particular product to run on the machine for 7.67
hours. What is the probability rate that it will fail between 1 to
7.67? I know I need to get the ALPHA and the BETA to complete my
calculations But I don't know how to get it. I know you could use
solver but I never used it before or can I use other formula functions
to get it? Also I cannot post the expected results because I just
don't know what they are. Here is the WEILBULL formula I'm using in
cell E8:

=WEIBULL(7.67,E2,E3,TRUE)-WEIBULL(1,E2,E3,TRUE)

I also posted this question to the link below.

http://www.mrexcel.com/board2/viewtopic.php?t=280395&highlight=


Does anyone know how to use solver or other formula functions to get
the ALPHA and the BETA ?
 
B

Billy Liddel

Fin
I'm not sure that you have enough data for a Weibull analysis but here is a
good page by William Dorner on how to use Excel with Weibull analysis.
http://www.qualitydigest.com/jan99/html/weibull.html

In the meantime why not try a simple probability, your data in your post is

Machine Name Run Time Rank
Cast machine-1 1.20 1 Schedule 6
Cast machine-1 1.99 2 P(Failure) 0.55
Cast machine-1 2.12 3 P(Success) 0.45
Cast machine-1 3.01 4
Cast machine-1 4.16 5
Cast machine-1 5.00 6
Cast machine-1 6.01 7
Cast machine-1 7.01 8
Cast machine-1 7.20 9
Cast machine-1 7.20 10
Cast machine-1 7.67 11

Total 52.57
Mean 4.779090909 2.72E-33
SD 2.404006882 1
95%+ 9.587104674
95%- -0.028922856
Skew -0.234037883
Var 5.779249091
Median 5.00

I sorted your data to make it clearer. The standard deviation, did not seem
to give a clear picture. In the end I just used Schedule enter a number,

P(Success) =ROUND(COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)

P(Failure) =ROUND(1-COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)


=WEIBULL(F2,$B$15,$B$16,TRUE) where F2 is the time you want to run the
machine, B15 is the average and B16 is the Standard Deviation. Frankly I
guessed that these are the values you need. it had a propability of 1 for the
machine machine failing after five hours.

I have just remembered you can find the standard deviation of probability.
For a 95% estimate we use 1.96 SDevs. So the Probability of Success would
look like this

Schedule 5
P(Success) 0.55
95% 0.294
P(Suc)hi 0.844
P(Suc)lo 0.256

P(Success) =: =ROUND(1-COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)
95% =: =1.96*SQRT(F3*(1-F3)/COUNT(B2:B12))
P(Success)hi=: =F3+F4
P(Success)lo=: =F3-F4

anyway I guess that you new that 5 hours is all you can hope for

Peter
 
F

Fin Fang Foom

Fin
I'm not sure that you have enough data for a Weibull analysis but here is a
good page by William Dorner on how to use Excel with Weibull analysis.http://www.qualitydigest.com/jan99/html/weibull.html

In the meantime why not try a simple probability, your data in your post is

Machine Name Run Time Rank
Cast machine-1 1.20 1 Schedule 6
Cast machine-1 1.99 2 P(Failure) 0.55
Cast machine-1 2.12 3 P(Success) 0.45
Cast machine-1 3.01 4
Cast machine-1 4.16 5
Cast machine-1 5.00 6
Cast machine-1 6.01 7
Cast machine-1 7.01 8
Cast machine-1 7.20 9
Cast machine-1 7.20 10
Cast machine-1 7.67 11

Total 52.57
Mean 4.779090909 2.72E-33
SD 2.404006882 1
95%+ 9.587104674
95%- -0.028922856
Skew -0.234037883
Var 5.779249091
Median 5.00

I sorted your data to make it clearer. The standard deviation, did not seem
to give a clear picture. In the end I just used Schedule enter a number,

P(Success) =ROUND(COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)

P(Failure) =ROUND(1-COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)

=WEIBULL(F2,$B$15,$B$16,TRUE) where F2 is the time you want to run the
machine, B15 is the average and B16 is the Standard Deviation. Frankly I
guessed that these are the values you need. it had a propability of 1 for the
machine machine failing after five hours.

I have just remembered you can find the standard deviation of probability.
For a 95% estimate we use 1.96 SDevs. So the Probability of Success would
look like this

Schedule 5
P(Success) 0.55
95% 0.294
P(Suc)hi 0.844
P(Suc)lo 0.256

P(Success) =: =ROUND(1-COUNTIF($B$2:$B$12,"<"&$F$2)/COUNT($B$2:$B$12),2)
95% =: =1.96*SQRT(F3*(1-F3)/COUNT(B2:B12))
P(Success)hi=: =F3+F4
P(Success)lo=: =F3-F4

anyway I guess that you new that 5 hours is all you can hope for

Peter


Thank You so much for the help Billy Liddel!


I tried formulas with my data set and its not coming out as i
expecting.

Here my data set with your data calculations.

History of the Hours
Machine Ran
Machine 1 7.01
Machine 1 4.16
Machine 1 7.67
Machine 1 5
Machine 1 2.12
Machine 1 6.01
Machine 1 7.2

( Resutls )
Schedule 7.67
P(Success) 0.14
95% 25.71%
P(Suc)hi 39.71%
P(Suc)lo -14.00%


( Formulas )
Schedule 7.67
P(Success) =ROUND(1-COUNTIF($B$3:$B$9,"<"&$B$12)/COUNT($B$3:$B$9),
2)
95% =1.96*SQRT(B13*(1-B13)/COUNT(B3:B9))
P(Suc)hi =B13+B14
P(Suc)lo =B14-B15

That you can see its not coming out right.

Then I used these formulas and it looks like its coming out right but
I'm not sure.

mean 5.595714286
sigma 1.980949748
alpha 2.615140091
beta 18.89546699
P(Suc)hi 90.97%


( Formulas )
mean AVERAGE(B3:B9)
sigma STDEV(B3:B9)
alpha D2*SQRT(EXP(GAMMALN(1+2/D3))-EXP(GAMMALN(1+1/D3))^2)
beta D2*EXP(GAMMALN(2+3/D3))

P(Suc)hi =1-WEIBULL(7.67,D4,D5,1)


Can verify this?
 
B

Billy Liddel

Fin
I did not agree with the percentages you got for the new data but at a quick
glance the weibull looks ok. However, I think that we have both got our
terminology wrong.
Weibull is the probability of failure. so the 91% weibull would mean that
out of 20 days we could expect 2 full days production. 20-.91*20.

This is similar to the figures to the siple probabilty I arrived at. (it's
always a good idea to have a simple canculation to go on)
My figures are:
P(success) E 20 runs
P 11.11% 2
SD95% 14.52% 3
P(hi) 25.63% 5
P(lo) 0.00% 0
-3.41%
I used zero for the lo P as you can't have minus machine working.
Combining tthe two sets of figures gives:

P(success) E 20 runs
P1 14.29% 3
SD95% 25.92%
Prob hi 40.21% 8
Prob(lo) 0.00% 0
-11.64%

This is slightly higher than the weibull figures but within a decent range.

When I get time to analyse the weibull figures I'll post back

Regards
Peter
 
F

Fin Fang Foom

Fin
I did not agree with the percentages you got for the new data but at a quick
glance the weibull looks ok. However, I think that we have both got our
terminology wrong.
Weibull is the probability of failure. so the 91% weibull would mean that
out of 20 days we could expect 2 full days production. 20-.91*20.

This is similar to the figures to the siple probabilty I arrived at. (it's
always a good idea to have a simple canculation to go on)
My figures are:
P(success) E 20 runs
P 11.11% 2
SD95% 14.52% 3
P(hi) 25.63% 5
P(lo) 0.00% 0
-3.41%
I used zero for the lo P as you can't have minus machine working.
Combining tthe two sets of figures gives:

P(success) E 20 runs
P1 14.29% 3
SD95% 25.92%
Prob hi 40.21% 8
Prob(lo) 0.00% 0
-11.64%

This is slightly higher than the weibull figures but within a decent range.

When I get time to analyse the weibull figures I'll post back

Regards
Peter
















- Show quoted text -

Thank You Billy Liddel!

Please let me know if the WEIBULL Function is the right scenario I
should be using to get the correct probability rate failure of the
machine.
 
B

Billy Liddel

Fin

I have slavishly copied the method by Dave ? using your figures. I came up
with Beta 2.296404 and alpha 6.485752 giving a weibull probability of failure
for 6.67 hours of 77%.

If you like, I'll send you my workings. peter_athertonAThotmail.com. do the
obvious with the AT.

Is it the best way? I don't know, what do you intend doing with the result.
For instance, how many widgits are produced during a run is it always the
same proportion of units produced by the hours or when there is a quick
failure has a lower number than expected been produced?

Ma
 
F

Fin Fang Foom

Fin

I have slavishly copied the method by Dave ? using your figures. I came up
with Beta 2.296404 and alpha 6.485752 giving a weibull probability of failure
for 6.67 hours of 77%.

If you like, I'll send you my workings. peter_athertonAThotmail.com. do the
obvious with the AT.

Is it the best way? I don't know, what do you intend doing with the result.
For instance, how many widgits are produced during a run is it always the
same proportion of units produced by the hours or when there is a quick
failure has a lower number than expected been produced?

Ma






- Show quoted text -


I'm here at work right now, later on today I will email you.

Thank You so much!
 
D

Dana DeLouis

Hi. I am not familiar with this method, so I'm studying this thread to
learn.
May I ask what equation you used to get 77%.
I'm not getting the same results, but again, I am probably doing something
wrong.

I was trying to follow along with a math program, but nothing was working
out.
I noticed that I had to swapped a & b for the equations to work out.
The author (W. Dorner) was good enough to mention near the bottom that
Excel's parameters are backwards!!
This seems to check with other programs.
Great! How long has that bug in Excel been there?
Thanks for the link...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top