WEIBULL formula to get probability rate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fin Fang Foom - 22 Jul 2007 04:35 GMT

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 ?

Reply to this Message

Billy Liddel - 22 Jul 2007 23:32 GMT

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

> Hi everyone,
>
[quoted text clipped - 17 lines]
> Does anyone know how to use solver or other formula functions to get
> the ALPHA and the BETA ?

Reply to this Message

Fin Fang Foom - 23 Jul 2007 03:51 GMT

On Jul 22, 3:32 pm, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:

> Fin
> I'm not sure that you have enough data for a Weibull analysis but here is a
[quoted text clipped - 76 lines]
> > Does anyone know how to use solver or other formula functions to get
> > the ALPHA and the BETA ?

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?

Reply to this Message

Billy Liddel - 23 Jul 2007 12:50 GMT

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

> Thank You so much for the help Billy Liddel!
>
[quoted text clipped - 48 lines]
>
> Can verify this?

Reply to this Message

Fin Fang Foom - 23 Jul 2007 14:07 GMT

On Jul 23, 4:50 am, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:

> Fin
> I did not agree with the percentages you got for the new data but at a quick
[quoted text clipped - 83 lines]
>
> - 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.

Reply to this Message

Billy Liddel - 23 Jul 2007 18:36 GMT

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

> > > Can verify this?- Hide quoted text -
> >
[quoted text clipped - 5 lines]
> should be using to get the correct probability rate failure of the
> machine.

Reply to this Message

Fin Fang Foom - 23 Jul 2007 20:27 GMT

On Jul 23, 10:36 am, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:

> Fin
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

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

Thank You so much!

Reply to this Message

Dana DeLouis - 24 Jul 2007 04:21 GMT

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

Signature

Dana DeLouis

> Fin
>
[quoted text clipped - 24 lines]
>> should be using to get the correct probability rate failure of the
>> machine.

Reply to this Message