Tuesday, May 27, 2014

Solving Gamma Distribution Problems in Excel 2010 and Excel 2013

excel, excel 2010, excel 2013, statistics, gamma distribution,gamma(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.

excel, excel 2010, excel 2013, statistics, gamma distribution,gamma(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.

excel, excel 2010, excel 2013, statistics, gamma distribution,gamma(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:

excel, excel 2010, excel 2013, statistics, gamma distribution,gamma(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:

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

excel, excel 2010, excel 2013, statistics, gamma distribution,gamma(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:

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

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at

2 comments:

  1. Бонусы казино онлайн УкраинаFebruary 25, 2024 at 8:40 AM

    This comment has been removed by the author.

    ReplyDelete
  2. Бонусы казино онлайн УкраинаFebruary 25, 2024 at 8:41 AM

    "Отличный выбор для любителей азартных игр! Бездепозитный бонус казино - идеальный способ начать свой путь к удаче без риска потерять собственные средства. Рады предложить вам этот уникальный шанс на сайте BonusCasinoUA.com. Здесь вы найдете огромный выбор игр, щедрые бонусы и захватывающие турниры. Не упустите возможность испытать удачу и получить выигрыш абсолютно бесплатно! Посетите наш сайт прямо сейчас и начните играть без риска с бездепозитным бонусом казино!" https://bonuscasinoua.com/

    ReplyDelete

Subscribe to: Post Comments (Atom)