Wednesday, May 28, 2014

Solving Beta Distribution Problems in Excel 2010 and Excel 2013

beta, beta distribution, statistics, excel, excel 2010, excel 2013(Click On Image To See a Larger Version)

This is one of the following six articles on Solving Problems With Other Distributions in Excel

Solving Uniform Distribution Problems in Excel 2010 and Excel 2013

Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013

Solving Exponential Distribution Problems in Excel 2010 and Excel 2013

Solving Beta Distribution Problems in Excel 2010 and Excel 2013

Solving Gamma Distribution Problems in Excel 2010 and Excel 2013

Solving Poisson Distribution Problems in Excel 2010 and Excel 2013

 

Overview of the Beta

Distribution

The beta distribution is a family of continuous probability distributions. This is evidenced by the smooth shape of the above graph of a beta distribution’s PDF (Probability Density Function) curve. The beta distribution is defined on the interval [0,1] and is defined by two positive shape parameters α and β. The Excel formulas that calculate the PDF and CDF of the beta distribution have optional inputs that enable the [0, 1] interval to be expanded to an arbitrary width.

The beta distribution is most often used to model events which are constrained to take place between a minimum and maximum time limit. In this case the beta distribution is used to calculate the probability that a project will be completed within a given period of time. For this reason, the beta distribution is often used for modeling project planning and control systems such as PERT (Project Evaluation and Review Technique) and CPM (Critical Path Method).

The beta distribution is used in general to model the variation of a variable across samples, such as the percentage of the day people spend watching television. The beta distribution can be applied to a wide variety of disciplines to model the behavior of a random variable limited to intervals of finite length. Examples include the modeling of the variability of soil properties and the proportions of the minerals in rocks in stratigraphy. One common use of the beta distribution is to model a defect rate. The first problem in this section will perform this type of analysis.

 

Beta Distribution’s PDF –

Probability Density Function

The following Excel-generated graph shows the beta distribution’s PDF (Probability Density Function) for as the X value (the completion time) goes from 0 to 1 with α = 8 and β = 10.

The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value.

beta, beta distribution, statistics, excel, excel 2010, excel 2013(Click On Image To See a Larger Version)

 

Beta Distribution’s CDF –

Cumulative Distribution Function

The following Excel-generated graph shows the Beta distribution’s CDF (Cumulative Distribution Function) for 10 degrees of freedom as the X value (the completion time) goes from a completion time of 0 to 1.

The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value.

beta, beta distribution, statistics, excel, excel 2010, excel 2013(Click On Image To See a Larger Version)

 

Beta CDF Problem 1 Solved in Excel

The defect rate of observed for units coming off of a production line can be described by the beta distribution with shape parameters α = 3 and β = 6. What is the probability that UP TO 30 percent of the units are observed to be defective during an inspection of a large lot of units produced on that production line?

The problem asks to calculate the probability of UP TO 30 percent so the beta’s CDF (Cumulative Distribution Function) will be used to solve this problem.

Shape parameters have already been given as follows: α = 3 and β = 6

Min and Max Times will be set at 0 and 1 because the range of possible percentages of defective units is 0 percent to 100 percent, which is 0 to 1.

The X value to be evaluated is X = k = 30 percent = 0.3

The Excel equation to solve the problem is as follows:

F(X=0.3;α=3,β=6) = BETA.DIST(k,α,β,TRUE,MIN Time, Max Time)

F(X=0.3;α=3,β=6) = BETA.DIST(0.3,3,6,TRUE,0,1) = 0.4482

There is a 44.82 percent chance that at least 30 percent of all units inspected from one large batch are defective. This agrees with the Excel-generated CDF graph which X = 0.3 corresponds with Y = 0.4438 as follows:

beta, beta distribution, statistics, excel, excel 2010, excel 2013(Click On Image To See a Larger Version)

 

Beta CDF Problem 2 Solved in Excel

The completion time for a project can be described by the beta distribution with shape parameters α = 3 and β = 6. What is the probability that completion of the project will take UP TO 27 hours if the min and max times for project completion are 15 hours and 45 hours?

The problem asks to calculate the probability of UP TO 27 hours so the beta’s CDF (Cumulative Distribution Function) will be used to solve this problem.

Shape parameters have already been given as follows: α = 3 and β = 6

Min and Max Times are given in the problem to be 15 hours and 45 hours.

The X value to be evaluated is X = k = 27 hours

The Excel equation to solve the problem is as follows:

F(X=27;α=3,β=6) = BETA.DIST(k,α,β,TRUE,MIN Time, Max Time)

F(X=27;α=3,β=6) = BETA.DIST(27,3,6,TRUE,15,45) = 0.6846

There is a 68.46 percent chance that the project will take up to 27 hours to complete. This agrees with the Excel-generated CDF graph which X = 27 hours corresponds with Y = 0.6846 as follows:

beta, beta distribution, statistics, excel, excel 2010, excel 2013(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

2 comments: