## Wednesday, May 28, 2014

### Solving Beta Distribution Problems in Excel 2010 and 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. (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. (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: (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: (Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP

#### 1 comment:

1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Project Centers in Chennai.

Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai