Tuesday, May 27, 2014

Interactive Graph of Gamma Distribution in Excel 2010 and Excel 2013

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

This is one of the following eleven articles on creating user-interactive graphs of statistical distributions in Excel

Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013

Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013

Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013

Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013

Interactive Graph of the t-Distribution’s PDF in Excel 2010 and Excel 2013

Interactive Graph of the t-Distribution’s CDF in Excel 2010 and Excel 2013

Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013

Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013

Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013

Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013

Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013

 

Gamma Distribution

Overview

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 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.

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.

 

Graphing the Gamma Distribution’s

PDF – Probability Density Function

 

Step 1 – Create a Count

The Count becomes the basis for the X and Y values of each data point on the graph. This count will contain 100 points that count from 0 to 100 in increments 1. There are many ways to create a count. This count uses the method ROW() – ROW($B) to increment each cell value in the column by 1.

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

 

Step 2 – Create the X Values

The X values for each data point is the time between events in a Poisson process. The X-Axis expander and X-Axis shifter values are part of this calculation because the width of the gamma PDF graph varies significantly when its scale and shape parameters change. The X axis must be properly sized to enable the gamma distribution’s PDF curve to be fully visible and fully expanded in the graph.

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

 

Step 3 – Create the Y Values

One Y value is created for each increment of the Count. The Y value of each data point is its PDF value. The gamma distribution’s PDF value requires the X value and its two parameters, the Shape parameter k and the Scale Parameter θ. The X axis often has to be shifted and expanded in order to view the entire PDF curve fully in a single graph.

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

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, gamma distribution, excel graph, excel chart, statistics,gamma,poisson(Click On Image To See Larger Version)

The process of creating an Excel area chart and connecting the user inputs to the chart is shown in detail in the section of this manual that provides instructions on how to create an interactive normal distribution PDF curve with outer tails.

 

Effect of Changing the Shape Parameter k

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 k increased from 1 to 10. The PDF curve is shifted significantly when k is increased.

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, gamma distribution, excel graph, excel chart, statistics,gamma,poisson(Click On Image To See Larger Version)

 

Effect of Changing the Scale Parameter θ

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 θ increased from 1 to 3. The PDF curve is expanded significantly when θ is increased.

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

 

Effect of Changing the X-Axis Expander

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

 

Graphing the 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, gamma distribution, excel graph, excel chart, statistics,gamma,poisson(Click On Image To See Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

No comments:

Post a Comment