MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• How can I estimate the probability that a machine will work without failing for at least 20 hours?

• How can I estimate the probability that hanging drywall in a building will take more than 200 hours?

The Weibull random variable is a continuous random variable that is often used to model the lifetime of a machine. If we have data about how long similar machines have lasted in the past, we can estimate the two parameters (alpha and beta) that define a Weibull random variable. You can then use the WEIBULL function in Microsoft Office Excel 2007 to determine probabilities of interest, such as an estimate of how long a machine will run without failing.

The Beta random variable is a continuous random variable that’s often used to model the duration of an activity. Given estimates of the minimum duration, maximum duration, mean duration, and the standard deviation of the duration, you can use the BETADIST function in Excel to determine probabilities of interest.

• How can I estimate the probability that a machine will work without failing for at least 20 hours?

• Suppose we have observed the lifetime of seven similar machines. The data we’ve collected about the machines is contained in the file Weibullest.xlsx, shown in Figure 59-1.

  Figure 59-1: Machine lifetime data

• Reliability engineers have found that the Weibull random variable is usually appropriate for modeling machine lifetimes. The Weibull random variable is specified by two parameters: alpha and beta. Based on our data, we find (using the AVERAGE and STDEV functions in cells B13 and B14) that, on average, a machine lasts 18.68 hours, with a standard deviation of 7.40 hours. After copying these values into cells G6 and G11 and running the Solver, we find estimates of alpha and beta that ensure that the Weibull random variable will have a mean and standard deviation matching our data. In our case, we find that alpha equals 2.725 and beta equals 21.003, as you can see in Figure 59-2. Any value you enter for alpha and beta in cells E2 and E3 for a Weibull random variable yields a mean (computed in cell E6) and standard deviation (computed in cell E11). Our Solver model varies alpha and beta until the mean and standard deviation of the Weibull distribution equal the mean and standard deviation of machine lifetime computed from our data.

  Figure 59-2: Estimates of alpha and beta for a Weibull random variable

• Here’s the syntax of the WEIBULL function:

  WEIBULL(x,alpha,beta,Cumulative)

• When Cumulative=True this formula results in the probability that a Weibull random variable with parameters alpha and beta is less than or equal to x. Changing True to False yields the height of the Weibull probability density function (pdf). Remember from Chapter 55, “An Introduction to Random Variables,” that the height of a pdf for any value x of a continuous random variable indicates the likelihood that the random variable assumes a value near x. Thus, if the Weibull density for 20 hours were twice the Weibull density for 10 hours, we would know that our machine is twice as likely to work for 20 hours before failing than to work for 10 hours before failing. We can now answer some questions involving probabilities of interest.

• What is the probability that a machine will last at least 20 hours?   This probability (41.6 percent) is computed in cell G16 with the formula 1–WEIBULL(20,alpha,beta,1). Essentially, this formula computes the area under the Weibull pdf to the right of 20 hours by subtracting 1 from the area to the left of 20 hours.

• What is the probability that a machine will last from 15 through 30 hours?   This probability (59.9 percent) is computed in cell G18 with the formula WEIBULL(30,alpha,beta, TRUE)–WEIBULL(15,alpha,beta,TRUE). This formula finds the area under the Weibull pdf from 15 through 30 hours by computing the area to the left of 30 hours less the area to the left of 15 hours. After we subtract the probability of a machine working without failure for less than 15 hours from the probability of a machine working without failure for less than or equal to 30 hours, we are left with the probability that a machine will work without failure from 15 through 30 hours.

• How can I estimate the probability that hanging drywall in a building will take more than 200 hours?

• Since the development of the Polaris missile in the 1950s, project managers have modeled activity durations with the Beta random variable. To specify a Beta random variable, you need to specify a minimum value, a maximum value, and two parameters (alpha and beta). The data in the file Beta.xlsx (see Figure 59-3) can be used to estimate the parameters of a Beta distribution.

  Figure 59-3: Determining probabilities with the Beta random variable

• Let’s estimate that the time needed to hang drywall in a building will be from 0 through 600 hours. These are our minimum and maximum values, entered in cells C7 and C8. The cell range F8:F22 contains the lengths of time needed to hang drywall in 15 buildings of a similar size. In cell F5, I used the AVERAGE function to compute the mean time (78.49 hours) needed to drywall these 15 buildings. In cell F6, I used the STDEV function to determine the standard deviation (47.97 hours) of the time needed to drywall these buildings. Any choice of values of alpha and beta determine the shape of the Beta distribution’s pdf and the mean and standard deviation for the corresponding Beta random variable. If we can choose alpha and beta values to match the mean and standard deviation of the drywall installation times computed from our data, it seems reasonable that these alpha and beta values will yield probabilities consistent with the observed data. After entering the mean and standard deviation of the drywall installation from our data in cells C9 and C10, our worksheet computes values for alpha (2.20) in cell C5 and beta (14.59) in cell C6 that ensure that the mean and standard deviation of the Beta random variable match the mean and standard deviation of our data.

• The function BETADIST(x,alpha,beta,lower,upper) determines the probability that a Beta random variable ranging from lower through upper, with parameters alpha and beta, assumes a value less than or equal to x. We can now use the BETADIST function to determine probabilities of interest.

• To compute the probability that hanging drywall will take at least 200 hours, we can use the formula in cell C15, 1–BETADIST(200,alpha,beta,lower,upper). The result is 2.1 percent. This formula simply computes the probability that hanging drywall will take at least 200 hours as 1–probability drywalling takes less than or equal to 200 hours.

• The probability that hanging drywall will take, at most, 80 hours (58.3 percent) can be computed with the formula in cell C16, BETADIST(80,alpha,beta,lower,upper). And to compute the probability that the task will take from 30 through 150 hours (77.1 percent), in cell C17 we use the formula BETADIST(150,alpha,beta,lower,upper)– BETADIST(30,alpha,beta,lower,upper). This formula computes the probability that drywalling takes from 30 through 150 hours as the probability that drywalling takes less than or equal to 150 hours minus the probability that drywalling takes less than or equal to 30 hours. The difference between these probabilities counts only instances when drywalling takes from 30 through 150 hours.