You haven't turned the page yet? Those of you who remain probably fall under one of two categories: those familiar with reliability data analysis, and Excel enthusiasts who are curious to learn one more way to exploit this versatile software. I predict readers in both groups will be glad they stuck around.

For the uninitiated, Weibull analysis is a method for modeling data sets containing values greater than zero, such as failure data. Weibull analysis can make predictions about a product's life, compare the reliability of competing product designs, statistically establish warranty policies or proactively manage spare parts inventories, to name just a few common industrial applications. In academia, Weibull analysis has modeled such diverse phenomena as the length of labor strikes, AIDS mortality and earthquake probabilities.

The data in Figure 1 don't clearly indicate whether either design meets the desired reliability goal. Both designs had at least one failure before 400,000 cycles, yet clearly the average number of cycles before failure exceeds 400,000 for both designs. A comparison of sample averages using a Student's t test reveals no statistical difference between the average cycles for Design A and the average cycles for Design B (p-value = 0.965). But as a simple measure of central tendency, the sample average gives no information about the spread or shape of the distribution of failure times. Could the two designs' averages be the same, but their reliability be quite different? How can you be more scientific about comparing the reliability of the two proposed designs?

1.Open Excel and into cell A1, type the label: Design A Cycles. Enter the failure data for Design A into cells A2:A11. Highlight cells A1:A11 and click on the Sort Ascending button to order the failure cycles from lowest to highest.

4.Type into cell D1 the label: 1/(1-Median Rank). Then, in D2, enter the formula: =1/(1-C2). Copy cell D2 down through cell D11.

5.Into cell E1, enter the label: ln(ln(1/(1-Median Rank))). In cell E2, type the formula: =LN(LN(D2)). Copy cell E2 down through cell E11.

6.Finally, you'll need to transform the Cycles data. In cell F1, type the label: ln(Design A Cycles). In cell F2, type the formula: =LN(A2). Copy cell F2 down through cell F11.

7.Again, compare your spreadsheet with Figure 2. After you confirm that everything is correct, save your workbook.

First, be sure that the Analysis ToolPak Add-In is loaded into Excel. From the menu bar, select Tools . Add-Ins. Click on the checkbox for Analysis ToolPak, and then click OK.

To perform the simple linear regression:

1.While on the page you just created, from the menu bar, select Tools and Data Analysis. Scroll down and highlight "Regression" and click OK. A data-entry window will pop up.

2.Under "Input Y Range," type: $E$1:$E$11.

3.For "Input X Range," type: $F$1:$F$11.

4.Click to add a checkmark in the box for "Labels."

5.For "Output Options," select "New Worksheet Ply."

6.Click to add a checkmark in the box for "Line Fit Plots."

7.Click OK. Excel will perform the regression and place the output on a new worksheet.

In cell A19, type the label: Beta (or Shape Parameter)=. In cell B19, type the formula: =B18. In cell A20, type the label: Alpha (or Characteristic Life)=. In cell B20, type the formula: =EXP(-B17/B18). Your results should closely resemble Figure 3. For Design A, b=4.25 and a=693,380.4

An identical analysis using the Design B data yields a  = 2.53 and an =723,105.

The Weibull characteristic life, called, is a measure of the scale, or spread, in the distribution of data. It so happens that equals the number of cycles at which 63.2 percent of the product has failed. In other words, for a Weibull distribution R( =0.368, regardless of the value of  . For example, with Design A housings, about 37 percent of the housings should survive at least 693,380 cycles.

While this is interesting, it still doesn't reveal whether either jack-in-the-box design meets the reliability goal of R(400,000) 0.90. For this, you need to know the formula for reliability assuming a Weibull distribution:

where  x is the time (or number of cycles) until failure.

The formula looks intimidating, but by simply plugging in the known values for , and x, you can obtain the desired reliability estimate.

Computing the above formulas can be confusing and laborious using a calculator. Besides, you can't visualize or compare the reliability of each design for multiple cycle values. Excel provides a better way.

2.In cell D1, type the label: Cycles.

3.In cells D2:D11, type the values 100,000-1 million in increments of 100,000.

4.In cell E1, type the label: Survival Probability.

5.In cell E2, type the formula: =WEIBULL(D2,$B$1,$B$2,TRUE).

6.Copy cell E2 down through cell E11.

7.In cell F1, type the label: Reliability.

8.In cell F2, type the formula: =1-E2.

9.Copy cell F2 down through cell F11.

Likewise, sometimes you'll need to compute the number of cycles (or time to failure) corresponding to a certain reliability level. For example, 99 percent of Design A housings will have failed by how many cycles?

Unfortunately, Excel doesn't have an inverse Weibull function. To perform this calculation (called solving for "critical values"), follow these steps:

1.On your Weibull reliability calculator worksheet, type in the label and values as shown in cells C13:C18 in Figure 5.

2.In cell D13, type the label: Cycles.

3.In cell D14, enter the formula: =$B$2*(-LN(C14))^(1/$B$1).

4.Copy cell D14 down through D18.

We find that for Design A of the jack-in-the-box, R(992,975)=0.01, or 99 percent of the housings will have failed by 992,975 cycles.

Figure 7 allows a comprehensive comparison of the two designs' survival rates. Note that at 400,000 cycles, about 90 percent of Design A housings have survived, whereas only about 80 percent of Design B housings have survived. Therefore, for the stated reliability goal of R(400,000) 0.90, Design A is clearly superior. However, about 10 percent of Design B housings will survive to 1 million cycles, vs. fewer than 1 percent of Design A. This graph clearly shows the importance of defining the reliability goal in order to choose the more desirable design.

Armed with this information, and knowing that the competition only offers a two-year warranty on its jack-in-the-boxes, your company might choose to be conservative and offer a five-year warranty. This would ensure domination of the competition from a marketing standpoint, yet still allow for warranty costs to stay at or below the desired levels.

The above example is somewhat simplistic. Interested readers can find more sophisticated illustrations of warranty strategy using Weibull analysis in academic articles, such as Jayprakash Patankar and Amitava Mitra's "Effects of Warranty Execution on Warranty Reserve Costs" (Management Science, 1995).

Confusion has arisen in the past due to the lack of standardized nomenclature for the Weibull cdf. Its creator, Waloddi Weibull, himself published multiple versions of this formula using different nomenclatures. Arthur Hallinan Jr. provides an excellent history of the various forms of the Weibull distribution in "A Review of the Weibull Distribution" (Journal of Quality Technology, 1993).

The format above is the most commonly accepted one. Unfortunately, in Excel, the "Help" screen for the "=WEIBULL" function gives the formula with the and parameters reversed (i.e., the characteristic life is labeled and the shape parameter is labeled).

The Weibull distribution's virtually limitless versatility is matched by Excel's countless capabilities. An astute data analyst who understands the theory behind a given analysis can often get results from Excel that others might assume require specialized statistical software. With Excel, Weibull analysis lies well within reach for most engineers with a statistics background.

The issues involved in analyzing and interpreting censored data are complex. Improper analysis of censored data can yield misleading results, which Margaret Mackisack and Ronald Stillman point out in "A Cautionary Tale About Weibull Analysis" (IEEE Transactions on Reliability, 1996). For further technical details about analyzing censored life data, readers also can consult Wayne Nelson's book Applied Life Data Analysis (John Wiley & Sons, 1982) or William Meeker and Luis Escobar's book Statistical Methods for Reliability Data (John Wiley & Sons, 1998).

4. Some software packages may give slightly different parameter estimates than the ones in this article. That is because these applications regress the transformed median ranks (Y) on the transformed lifetimes (X) rather than vice versa. "Simulation studies show that Y on X regression produces almost double the bias in the estimation of the shape parameter as the X on Y regression," according to Dodson. Moreover, the universal convention for displaying a Weibull probability plot is to depict "ln(lifetime)" on the horizontal axis. The regression method presented in this article automatically generates the plot in this standard format.

5. The type of Weibull distribution discussed in this article is called the two-parameter Weibull distribution. This simple form is adequate for a majority of Weibull analysis scenarios. However, if the transformed failure data plot has a curved rather than a straight line appearance, or if is found to be greater than 6.0, then a third parameter may be needed to adequately model the data. The third parameter, included in the aptly named three-parameter Weibull distribution, effectively shifts the entire distribution to the right. This location parameter is most commonly called (the Greek letter gamma). In practice, can be interpreted as the earliest possible time at which failure may occur. Of course, may never be larger than the value of the earliest failure from the data set. Readers who encounter a curved regression plot or a value greater than 6.0 should consult Hallinan's article or John McCool's article "Inference on the Weibull Location Parameter" (Journal of Quality Technology, 1998) for guidance on fitting a three-parameter Weibull model.