Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
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/viewto...395&highlight= Does anyone know how to use solver or other formula functions to get the ALPHA and the BETA ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
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 "Fin Fang Foom" wrote: 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/viewto...395&highlight= Does anyone know how to use solver or other formula functions to get the ALPHA and the BETA ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
On Jul 22, 3:32 pm, Billy Liddel
wrote: 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 "Fin Fang Foom" wrote: 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/viewto...395&highlight= 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
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 a 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 "Fin Fang Foom" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
On Jul 23, 4:50 am, Billy Liddel
wrote: 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 a 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 "Fin Fang Foom" wrote: 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?- Hide quoted text - - 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
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 - - 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
On Jul 23, 10:36 am, Billy Liddel
wrote: 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 - - 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.- Hide quoted text - - Show quoted text - I'm here at work right now, later on today I will email you. Thank You so much! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WEIBULL formula to get probability rate
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... -- Dana DeLouis "Billy Liddel" wrote in message ... 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 - - 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Little help with weibull function needed | Excel Worksheet Functions | |||
Weibull paper in Excel - how? | Charts and Charting in Excel | |||
Prime rate/Liber rate into sheet automatically? | Excel Discussion (Misc queries) | |||
Real RATE of return using =RATE illusive, inflation adjusted inflo | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions |