## Tuesday, May 27, 2014

### Solving Gamma Distribution Problems in Excel 2010 and Excel 2013

(Click On Image To See 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 Gamma Distribution

The gamma distribution is a family of continuous probability distributions. This is evidenced by the smooth shape of the above graph of a gamma distribution’s PDF (Probability Density Function) curve.

The gamma distribution describes the distribution of waiting times between Poisson-distributed events. The gamma distribution function is characterized by 2 variables, its shape parameter k and its scale parameter θ (Theta).

The gamma distribution calculates the probability of a specific waiting time until the kth Poisson event occurs if θ = 1/λ is the mean number of Poisson-distributed events per time unit. For example, if the mean time between the Poisson-distributed events is 2 minutes, then θ = ½ = 0.5.

The gamma distribution is closely related to the exponential distribution. The gamma distribution calculates the probability of a specific waiting time until the kth event Poisson occurs. The exponential distribution calculates the probability of a specific waiting time until the 1st Poisson event occurs.

Applications of the gamma distribution are often based on intervals between Poisson-distributed events. Examples of these would include queuing models, the flow of items through manufacturing and distribution processes, and the load on web servers and many forms of telecom.

Due to its moderately skewed profile, it can be used as a model in a range of disciplines, including climatology where it is a working model for rainfall, and financial services where it has been used for modeling insurance claims and the size of loan defaults. It has therefore been used in probability of ruin and value-at-risk equations.

## Gamma Distribution’s PDF – Probability Density Function

The following Excel-generated graph shows the gamma distribution’s PDF (Probability Density Function) for as the X value goes from 0 to 10 with Shape parameter k = 2 and Scale parameter θ = 1.

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 Larger Version)

## Gamma Distribution’s CDF – Cumulative Distribution Function

The following Excel-generated graph shows the gamma distribution’s CDF (Cumulative Distribution Function) for k = 2 and θ = 1 as the X value goes from 1 to 10.

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 Larger Version)

### Characteristics of Poisson-Distributed Events

The Poisson distribution is used for situations that involve counting events over identical intervals of time or objects over identical intervals of volume. If each count is independent of the others, the probability of an event occurring in any of the intervals is constant, and the average count is known, the Poisson distribution can be used to calculate the probability of a specific number of events occurring in an interval.

The Poisson distribution has only one parameter: the rate parameter λ. The rate parameter λ (Lamda) equals the average number of occurrences over the intervals. λ also equals the variance in the number of occurrences over the intervals. One check of whether data are Poisson-distributed is whether the mean number of occurrences equals the variance n the number of occurrences over the intervals.

The Poisson distribution is based upon the following four assumptions:

1) The probability of an event occurring remains constant in all intervals.

2) All events are independent of each other and do not overlap.

3) The probability of observing a single event over a small interval is approximately proportional to the size of that interval.

4) The mean number of occurrences per interval (λ) and the variance in the number of occurrence per interval are approximately the same.

## Gamma CDF Problem Solved in Excel

Calls to a customer service desk are Poisson-distributed and arrive on average every 2 minutes. Calculate the probability that 3 successive calls arrive within a time interval that is UP TO 4 minutes long.

The problem asks to calculate the probability that the wait time will be UP TO 4 minutes so the gamma’s CDF (Cumulative Distribution Function) will be used to solve this problem.

If a call comes in on average every 2 minutes, the scale parameter θ = 2.

The X value to be evaluated is X = 4 minutes

The Excel equation to solve the problem is as follows:

F(X=4;k=3,θ=2) = GAMMA.DIST(X,k,θ,TRUE) = GAMMA.DIST(4,3,2,TRUE) = 0.3233

There is a 32.33 percent probability that the time interval in which 3 successive calls arrive will UP TO 4 minutes. This agrees with the CDF graph which X = 4 corresponds with Y = 0.3233 as follows:

(Click On Image To See Larger Version)

The exponential distribution equals the gamma distribution when gamma distribution parameters are set as follows:

k = 1

θ = 1/λ

In this case the following is true:

EXPON.DIST(X, λ, FALSE) = GAMMA.DIST(X, 1, 1/λ, FALSE)

and

EXPON.DIST(X, λ, TRUE) = GAMMA.DIST(X, 1, 1/λ, TRUE)

The gamma distribution calculates the probability of wait time for the kth Poisson event. Setting k to 1 configures the gamma distribution to calculate the probability of wait time for the first Poisson event if the average rate is λ. The exponential distribution also calculates the probability of wait time to the first Poisson event when the average rate time is λ. The following two images confirm this:

(Click On Image To See Larger Version)

(Click On Image To See Larger Version)

The gamma distribution equals the Chi-Square distribution when gamma distribution parameters are set as follows:

k = df/2

θ = 2

In this case the following is true:

GAMMA.DIST(X, df/2, 2, FALSE) = CHISQ.DIST(X, df, FALSE)

and

GAMMA.DIST(X, df/2, 2, TRUE) = CHISQ.DIST(X, df, TRUE)

The following image shows this:

(Click On Image To See 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