Gamma distribution in Excel: waiting times, totals and GAMMA.INV
In short: the total time of several waits in a row, such as three repairs one after another. More generally, a positive quantity with a tail to the right.
The gamma distribution describes a positive quantity with a right tail, and in particular the total time of several random steps in a row. A shape and a scale set it; its mean is shape × scale. In Excel, =GAMMA.INV(RAND(), shape, scale) draws from it. Three repairs in a row, each taking 2 days on average with exponential spread, take a gamma-distributed total with shape 3 and scale 2: 6.0 days on average, more than 10 days in 12.5% of cases.
What a gamma distribution is
Start from the exponential distribution, the waiting time for one random event. Wait for several such events in a row, and the total is gamma-distributed: the shape is the number of steps and the scale the mean of each. The shape need not be a whole number, which makes the gamma a flexible family for any positive, right-skewed quantity: rainfall, insurance claims, durations, demand.
How to read the chart: each line is the total time of 1, 3 or 8 waits in a row. With one wait, short times are the most common; the more waits add up, the more the total gathers around its average.
Formulas: mean, standard deviation, peak, and from a mean and standard deviation
| Quantity | Formula and example |
|---|---|
| Mean | k × θ = 6.0 days |
| Standard deviation | √k × θ = 3.46 days |
| Peak (mode) | (k − 1) × θ, for k ≥ 1 = 4.0 days |
| From a mean and standard deviation | k = (mean / sd)², θ = sd² / mean |
The gamma distribution in Excel
GAMMA.INV(probability, alpha, beta) takes the shape as alpha and the scale as beta. Some textbooks write the gamma with a rate, 1 / scale, instead; Excel uses the scale, so a rate of 0.5 per day is a beta of 2. In one cell a draw is =GAMMA.INV(RAND(),D17,F17), and GAMMA.DIST(x, alpha, beta, TRUE) gives the chance of a value below x: =1-GAMMA.DIST(10, 3, 2, TRUE) is the 12.5% chance of taking more than 10 days.
| Cell | Formula | Value |
|---|---|---|
| Random number u (I17) | =RAND() | |
| Draw (J17) | =GAMMA.INV(I17, | |
| Draw in one cell | =GAMMA.INV(RAND(), | |
| Mean (K17) | =D17*F17 | 6.00 |
| P10 (L17) | =GAMMA.INV(0.1, | 2.20 |
| P90 (M17) | =GAMMA.INV(0.9, | 10.64 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 6.00 | 5.97 | 6.00 |
| Standard deviation | 3.46 | 3.50 | 3.46 |
| P10 | 2.20 | 2.19 | 2.20 |
| P50 (median) | 5.35 | 5.28 | 5.35 |
| P90 | 10.64 | 10.69 | 10.64 |
Excel draws with RAND(), fully at random; xellstorm spreads its draws evenly over the range (Latin Hypercube sampling), so its statistics land closer to the exact values.
Gamma or lognormal?
Both are positive and skewed to the right, and with the same mean and standard deviation they look alike in the middle. The difference is in the tail: the lognormal’s is heavier. Matched to the repairs’ mean of 6.0 and standard deviation of 3.46, a lognormal gives a 0.6% chance of more than 20 days, the gamma 0.3%. When rare, very large values matter (losses, overruns), that difference matters too: fit both to your data and compare, or choose the heavier tail to be prudent. See the lognormal distribution.
Entering a gamma distribution in xellstorm
- Make the cell an input and, on the Distributions step, choose Gamma. Type a (the shape) and scale; loc shifts the start away from zero, usually 0.
- With past values, “From data” in the side panel fits a gamma starting at zero, next to lognormal, Weibull and other shapes, and ranks them.
“Import from workbook” converts =GAMMA.INV(RAND(), alpha, beta) into a gamma input, and add-in models convert from RiskGamma, RiskErlang, VoseGamma and PsiGamma.
Questions
How do I generate gamma-distributed random numbers in Excel?
Use =GAMMA.INV(RAND(), shape, scale), for example =GAMMA.INV(RAND(), 3, 2) for a mean of 6.0. Each recalculation draws a new value.
What are the shape and scale of a gamma distribution?
The shape sets the form: 1 is an exponential distribution, larger values give a hump that grows more symmetric. The scale stretches it: the mean is shape × scale and the standard deviation √shape × scale.
What is the relationship between the gamma and the exponential distribution?
An exponential distribution is a gamma with shape 1. The sum of several independent exponential times with the same mean is a gamma with the number of times as its shape and that mean as its scale (also called an Erlang distribution).
Related
- Guide
Which probability distribution should I use? A guide for Monte Carlo models
Find the right probability distribution in two questions: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta and more, with what each is for and its Excel formula. - Guide
Lognormal distribution in Excel: LOGNORM.INV, parameters and when to use it
A lognormal distribution is positive with a long right tail. How LOGNORM.INV’s parameters work, how to get them from a mean and standard deviation, and when to use it. - Guide
Weibull distribution in Excel: shape, scale and time to failure
The Weibull distribution models time to failure: shape below 1 for early failures, 1 for random ones (exponential), above 1 for wear-out. Excel formulas, B10 life and the exponential case.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.