# Baikos Weibull Estimation Spreadsheet by TaylorRandle

VIEWS: 48 PAGES: 11

• pg 1
```									                                   6a8cb18c-08a4-4010-bafb-67ee85779552.xls

1      2       3          4         5      6       7    8     9  10 11 12   13
1   MAXIMUM LIKELIHOOD ESTIMATION OF WEIBULL PARAMETERS WITH A WORKSHEET
2                       Larry George
3               Oct. 23, 1987 revised July 3, 1989
4              Tables 1, 2, and 3 installed in May 1990.
5                 Table 6 installed Sept. 25, 1991
6       This worksheet computes the maximum likelihood estimator (mle)
7   of the two parameter Weibull distribution function. The Weibull
8   distribution function F(y) is defined by
9
10           1 - F(y) = P[Life > y] = exp[-(y/a)^b].
11
12   The two parameters to be estimated are a and b. Data may be
13   multiply censored; i.e. some data may be observed lives of unfailed
14   items and some may be the lives of failed items. Reference [1] gives
15   the mle computation method. It requires a 1-dimension search for the
16   mle of b. The worksheet automatically computes the mle of a and the
17   mode from the value of b.
18       Study my data and formulas before modifying the worksheet to
19   compute estimators from your data. (Columns A and B contain Terry
20   Baiko's rolling reliability data); column A indicates whether the
21   observed life in column B was terminated by failure or not. At the
22   bottom of column A is the total number of failures. Column C
23   contains the lives from column B raised to the power b. At the bottom
24   of column C are the sums of the column entries and the mle of a.
25   Column D contains the natural logarithms of the lives. Column E
26   contains terms used to compute the mle of b. At the bottom of column
27   D are the mle of b and my guess for b. Column F uses the mle of a and
28   the mle of b to compute an estimate of the Weibull 1-F(y) at the data
29   points.
30       Table 6. computes the regression estimators of the parameters.
31   You may want to use the regression estimate of b as a guess to start
32   the maximum likelihood estimation.
33       The worksheet also constructs the Kaplan-Meier estimate of the
34   complement of the cumulative distribution function, reference [2].
35   Column G contains the Kaplan-Meier estimate of 1 - F(y).
36       Terry's data had a lot of survivors after the last failure. The
37   number is imbedded in the formula for the last row, column C.
38       The mle of the mode is below column G.
39       Once you see how I set up the worksheet, use it for your data.
40   Copy your life data in column B in place of mine. Insert or delete
41   rows in the middle of my data if you have more or fewer samples.
42   Indicate a failure with a 1 and a survivor with a 0 in column A.
43       To compute the mle of b, input a positive starting value for
44   the parameter b in the cell to the right of the label "b =", under
45   the cell to the right of the label "b(mle)=". Hit calc (splat-= in
46   Excel) and the spreadsheet will compute its estimate of b under
47   column E. Change the value of b in the upper left hand corner toward
48   the value under column E. Hit calc again. Repeat until the two
49   b-values converge. Read the mle of a under column C. If the
50   computation doesn't converge, try different starting values for b.
51       You may also have to revise the Kaplan-Meier formulas if you

Page 1
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

1     2        3         4         5      6     7                       8          9         10           11          12          13
52   have different failures than Terry had. The
53   Kaplan-Meier estimate changes only at failure values.
54
55                       REFERENCES
56   [1] Wayne Nelson, "Applied Life Data Analysis", Wiley, New York,
57   1982
58   [2] E. L. Kaplan and P. Meier, "Nonparametric Estimation from
59   Incomplete Observations", J. Am. Stat. Assoc., Vol. 53, pp 457-481,
60   1958
61
62   Table 1. Estimate Weibull cdf of time to failure of computer.
63                                                       y        Weibull 1-F(y) -->                                      Failure rates
64   fail?=1 time hrs a           sumlnxi         b                                   logic boards
t, hours all analog boards HDA                       Kaplan-MeierWeibull Empirical
65        1       1 1.0E+00                0           0        1 0.999 0.9999 0.9997 0.9998                    0.9985 0.0005 0.0015
66        1       1 1.0E+00                0           0        1 0.999 0.9999 0.9997 0.9998                    0.9985 0.0005                 0
67        1       1 1.0E+00                0           0        1 0.999 0.9999 0.9997 0.9998                    0.9985 0.0005                 0
68        1       2 1.4E+00 0.69315 -0.0216                     2 0.999 0.9998 0.9996 0.9997                     0.997 0.0003 0.0015
69        1       2 1.4E+00 0.69315 -0.0216                     2 0.999 0.9998 0.9996 0.9997                     0.997 0.0003                 0
70        1       2 1.4E+00 0.69315 -0.0216                     2 0.999 0.9998 0.9996 0.9997                     0.997 0.0003                 0
71        1     10 2.9E+00 2.30259 -0.0717                    10 0.997 0.9995 0.9991 0.9994                     0.9965 0.0001 6E-05
72        1     13 3.3E+00 2.56495 -0.0799                    13 0.997 0.9994 0.999 0.9993                      0.9961 0.0001 0.0002
73        1     24 4.4E+00 3.17805 -0.0989                    24 0.995 0.9992 0.9986 0.9991                     0.9946 9E-05 0.0001
74        1     24 4.4E+00 3.17805 -0.0989                    24 0.995 0.9992 0.9986 0.9991                     0.9946 9E-05                  0
75        1     24 4.4E+00 3.17805 -0.0989                    24 0.995 0.9992 0.9986 0.9991                     0.9946 9E-05                  0
76        1     28 4.8E+00 3.3322 -0.1036                     28 0.995 0.9991 0.9985 0.9991                     0.9941 8E-05 0.0001
77        1     34 5.2E+00 3.52636 -0.1096                    34 0.995 0.999 0.9984 0.999                       0.9936 8E-05 8E-05
78        1     65 7.0E+00 4.17439 -0.1295                    65 0.993 0.9984 0.9977 0.9986                     0.9931 5E-05 2E-05
79        1     70 7.3E+00 4.2485 -0.1318                     70 0.992 0.9983 0.9976 0.9986                     0.9926 5E-05 1E-04
80        1     72 7.4E+00 4.27667 -0.1326                    72 0.992 0.9983 0.9976 0.9985                     0.9916 5E-05 0.0005
81        1     72 7.4E+00 4.27667 -0.1326                    72 0.992 0.9983 0.9976 0.9985                     0.9916 5E-05                  0
82        1     84 7.9E+00 4.43082 -0.1373                    84 0.992 0.9981 0.9974 0.9984                     0.9911 5E-05 4E-05
83        1     88 8.1E+00 4.47734 -0.1387                    88 0.992 0.9981 0.9973 0.9984                     0.9906 5E-05 0.0001
84        1     90 8.2E+00 4.49981 -0.1394                    90 0.991 0.9981 0.9973 0.9984                     0.9901 4E-05 0.0002
85        1 113 9.1E+00 4.72739 -0.1463                      113 0.99 0.9978 0.9969 0.9982                      0.9896 4E-05 2E-05
86        1 140 1.0E+01 4.94164 -0.1528                      140 0.989 0.9974 0.9966 0.998                      0.9891 4E-05 2E-05
87        1 148 1.0E+01 4.99721 -0.1545                      148 0.989 0.9974 0.9965 0.998                      0.9886 3E-05 6E-05
88        1 160 1.1E+01 5.07517 -0.1568                      160 0.989 0.9972 0.9963 0.9979                     0.9877 3E-05 8E-05
89        1 160 1.1E+01 5.07517 -0.1568                      160 0.989 0.9972 0.9963 0.9979                     0.9877 3E-05                  0
90        1 169 1.1E+01 5.1299 -0.1585                       169 0.989 0.9971 0.9962 0.9978                     0.9872 3E-05 6E-05
91        1 192 1.2E+01 5.2575 -0.1623                       192 0.988 0.9969 0.9959 0.9977                     0.9867 3E-05 2E-05
92        1 256 1.3E+01 5.54518 -0.1709                      256 0.986 0.9963 0.9953 0.9974                     0.9862 3E-05 8E-06
93        1 264 1.4E+01 5.57595 -0.1718                      264 0.986 0.9962 0.9952 0.9974                     0.9857 3E-05 6E-05
94        1 330 1.5E+01 5.79909 -0.1784                      330 0.984 0.9956 0.9946 0.9971                     0.9847 2E-05 2E-05
95        1 332 1.5E+01 5.80513 -0.1785                      332 0.984 0.9956 0.9946 0.9971                     0.9842 2E-05 0.0003
96        1 336 1.5E+01 5.81711 -0.1789                      336 0.984 0.9956 0.9946 0.997                      0.9837 2E-05 0.0001
97        0 336 3.0E+04 5.81711 5.77211                      336 0.984 0.9956 0.9946 0.997                      0.9837 2E-05                  0
98   ------- ------- ----------- ----------- ----------- -------- -------- ---------- ---------- ----------   ------------------------------------------------
99      32 2033 30531.2 b(mle)= 0.46782 mode =                           0
100           a = 2339627             b=       0.46782 Median##### hours
101   Weibull mean is b*Gamma(1/b+1) --> MTBF #####
102

Page 2
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

1      2       3         4         5      6      7        8       9         10     11             12   13
103   Table 2. Estimate Weibull cdf of time to failure of computer analog boards.
104                                                                   n(t)       d(t)    t
105   fail?=1time hrs a       sumlnxi      b          weiccdfKaplan-Meier2033         13       0
106   1      0      2.2E-06 -20.723 2.30259 1E-09            1
107        1     2 1.5E+00 0.69315 -0.077           2        1 0.9995
108        1    24 7.4E+00 3.17805 -0.3528         24 0.999 0.999        2020          1       2
109        1    70 1.5E+01 4.2485 -0.4713          70 0.998 0.9985       2023          1      24
110        1    72 1.5E+01 4.27667 -0.4744         72 0.998 0.9975       2020          1      70
111        1    72 1.5E+01 4.27667 -0.4744         72 0.998 0.9975       2019          2      72
112        1    90 1.7E+01 4.49981 -0.499          90 0.998 0.997        2018          1      90
113        1 192 2.7E+01 5.2575 -0.5823           192 0.997 0.9965       2015          1    192
114        1 330 3.9E+01 5.79909 -0.6415          330 0.996 0.996        2014          1    330
115        1 336 3.9E+01 5.81711 -0.6435          336 0.996 0.9951       2013          2    336
116        0 336 7.9E+04 5.81711 5.80424          336 0.996 0.9951
117   Ignore failure at time zero     -----------     --------        ----------
118        9 2034     79074 b(mle)= 0.62971 mode =           0
119          a = 1831571        b=     0.62971 Median##### hours
120   Weibull mean is b*Gamma(1/b+1) --> MTBF #####
121
122   Table 3. Estimate Weibull cdf of time to failure of computer logic boards.
123                                                                          n(t)       d(t)    t
124   fail?=1 time hrs a           sumlnxi         b                            2033
weiccdfKaplan-Meier            13      0
125        1       2 1.4E+00 0.69315 -0.063                 2        1 0.999
126        1       2 1.4E+00 0.69315 -0.063                 2        1 0.999    2020          2       2
127        1     13 3.8E+00 2.56495 -0.2329                13 0.999 0.9985      2019          1      13
128        1     24 5.2E+00 3.17805 -0.2885                24 0.999 0.998       2018          1      24
129        1     28 5.6E+00 3.3322 -0.3025                 28 0.999 0.9975      2017          1      28
130        1 140 1.3E+01 4.94164 -0.4477                  140 0.997 0.997       2016          1     140
131        1 160 1.4E+01 5.07517 -0.4597                  160 0.996 0.9965      2015          1     160
132        1 169 1.4E+01 5.1299 -0.4646                   169 0.996 0.996       2014          1     169
133        1 264 1.8E+01 5.57595 -0.5045                  264 0.995 0.9955      2013          1     264
134        1 330 2.0E+01 5.79909 -0.5244                  330 0.995 0.9951      2012          1     330
135        1 332 2.0E+01 5.80513 -0.5249                  332 0.995 0.9946      2011          1     332
136        0 336 4.1E+04 5.81711 5.80065                  336 0.995 0.9941      2010          1     336
137   ------- ------- ----------- ----------- -----------     --------       ----------
138      11 2132 41603.4 b(mle)= 0.51946 mode =                      0
139           a = 7715375             b=       0.51946 Median##### hours
140   Weibull mean is b*Gamma(1/b+1) --> MTBF #####
141
142   Table 4. Estimate Weibull cdf of time to failure of computer HDA, all brands.
143                                                                           n(t)       d(t)   t
144   fail?=1 time hrs a           sumlnxi         b          weiccdfKaplan-Meier2033         5      0
145        1       1 1.0E+00                0           0   1        1 0.9995
146        1     10 2.9E+00 2.30259 -0.2092                10 0.999 0.999        2028         1       1
147        1     65 6.9E+00 4.17439 -0.3788                65 0.999 0.9985       2027         1      10
148        1 148 1.0E+01 4.99721 -0.4531                  148 0.998 0.998        2026         1      65
149        1 160 1.0E+01 5.07517 -0.4601                  160 0.998 0.9975       2025         1     148
150        1 256 1.3E+01 5.54518 -0.5024                  256 0.997 0.997        2024         1     160
151        0 336 3.0E+04 5.81711 4.16717                  336 0.997 0.997        2023         1     256
152   ------- ------- ----------- ----------- -----------     --------        ----------
153        6 2032 29847.5 b(mle)= 0.46218 mode =                     0

Page 3
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

1      2      3      4        5      6     7      8       9     10   11   12   13
154        a=     1E+08    b=     0.46218 Median##### hours
155   Weibull mean is b*Gamma(1/b+1) --> MTBF #####
156
157   Table 5. Constants for an approximation to the gamma function
158   From Press et al "Numerical Recipes…"
159   gamma 5
160   cee0      1
161   cee1 76.2
162   cee2 -87
163   cee3    24
164   cee4 -1.2
165   cee5      0
166   cee6     -0
167   stp   2.51
168
169   Table 6. Regression estimates of Weibull parameters
170                                          Regression
171   fail?=1      R(t)
time hrs      ln(t)  ln -lnR(t) b     a
172        1     1 0.99852       0 -6.5145 0.378 -6.37 <-coeffs
173        1     1 0.99852       0 -6.5145 0.012 0.048 <-stderr
174        1     1 0.99852       0 -6.5145 0.972 0.122 <-R^2,Sy
175        1     2 0.99704 0.69315 -5.8206 1044        30 <-F,DofF
176        1     2 0.99704 0.69315 -5.8206 15.45 0.444 <-RSS,SSE
177        1     2 0.99704 0.69315 -5.8206 beta alpha
178        1    10 0.99654 2.30259 -5.6662 0.378 #####
179        1    13 0.99605 2.56495 -5.5324
180        1    24 0.99457 3.17805 -5.2132
181        1    24 0.99457 3.17805 -5.2132
182        1    24 0.99457 3.17805 -5.2132
183        1    28 0.99408 3.3322 -5.1259
184        1    34 0.99358 3.52636 -5.0457
185        1    65 0.99309 4.17439 -4.9713
186        1    70 0.9926 4.2485 -4.9021
187        1    72 0.99161 4.27667 -4.7764
188        1    72 0.99161 4.27667 -4.7764
189        1    84 0.99112 4.43082 -4.719
190        1    88 0.99062 4.47734 -4.6647
191        1    90 0.99013 4.49981 -4.6131
192        1 113 0.98963 4.72739 -4.5641
193        1 140 0.98914 4.94164 -4.5173
194        1 148 0.98865 4.99721 -4.4726
195        1 160 0.98766 5.07517 -4.3887
196        1 160 0.98766 5.07517 -4.3887
197        1 169 0.98717 5.1299 -4.3493
198        1 192 0.98667 5.2575 -4.3113
199        1 256 0.98618 5.54518 -4.2747
200        1 264 0.98569 5.57595 -4.2393
201        1 330 0.9847 5.79909 -4.1721
202        1 332 0.98421 5.80513 -4.1401
203        1 336 0.98371 5.81711 -4.1091

Page 4
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

14   15   16         17        18       19        20      21   22   23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

Page 5
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

14            15       16           17                  18                         19          20         21          22         23
52
53
54
55
56
57
58
59
60
61
62
63 n(t)           d(t)       t            P[DoA]
64         2039           13          0   0.006376
1
65                                        Kaplan-Meier
66         2026            3          1   0.998519
67         2023            3          2   0.997038                                  0.998
68         2020            1        10    0.996545
69         2019            1        13    0.996051                                  0.996
70         2018            3        24    0.994571
71         2015            1        28    0.994077
Reliability, P[Life > t]

0.994
72         2014            1        34    0.993583
73         2013            1        65      0.99309                                                                                 all
74         2012            1        70    0.992596                                  0.992
analog boards
75         2011            2        72    0.991609
logic boards
76         2009            1        84    0.991115                                   0.99
77         2008            1        88    0.990622                                                                                  HDA
78         2007            1        90    0.990128                                  0.988
79         2006            1       113    0.989635
80         2005            1       140    0.989141
81         2004            1       148    0.988648                                  0.986
82         2003            2       160      0.98766
83         2001            1       169    0.987167                                  0.984
84         2000            1       192    0.986673
85         1999            1       256      0.98618                                 0.982
86         1998            1       264    0.985686
0            200             400
87         1997            2       330    0.984699
88         1995            1       332    0.984205                                               Age-at-failure, hours
89         1994            1       336    0.983712
90
91                                                                                               Failure rates
92
93                             0.0015
94
95
96
97
98 --------------               0.001
99
Empirical
100
101                                                                                                                                             Weibull

102
0.0005

Page 6
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

14           15           16           17      18          19           20     21         22         23
0.0005
103
104                P[DoA]
105                0.006394
106
107   ln(1-F(t))   Kaplan-Meier         0
108    0.000495    0.999505                 0    50    100    150        200     250   300   350        400
109     0.00099    0.999011                                         Age, hours
110    0.001485    0.998516
111    0.002476    0.997527
112    0.002972    0.997033
113    0.003468    0.996538
114    0.003965    0.996043
115    0.004959    0.995054
116
117
118
119
120
121
122
123                P[DoA]
124                0.006394
125   ln(1-F(t))   Kaplan-Meier
126    0.000991      0.99901
127    0.001486    0.998515
128    0.001982      0.99802
129    0.002478    0.997525
130    0.002974    0.997031
131     0.00347    0.996536
132    0.003967    0.996041
133    0.004464    0.995546
134    0.004961    0.995051
135    0.005458    0.994557
136    0.005956    0.994062
137
138
139
140
141
142
143                P[DoA]
144                0.002459
145   ln(1-F(t))   Kaplan-Meier
146    0.000493    0.999507
147    0.000987    0.999014
148     0.00148    0.998521
149    0.001974    0.998028
150    0.002469    0.997535
151    0.002963    0.997041
152
153

Page 7
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

14   15   16         17        18       19        20      21   22   23
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

Page 8
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

24
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

Page 9
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

24
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
analog boards
75
logic boards
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
Empirical
100
Weibull
101
102

Page 10
6a8cb18c-08a4-4010-bafb-67ee85779552.xls

24
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

Page 11

```
To top