Please note javascript is required for full website functionality.
MVP

Blog

A to Z of Excel Functions: the GAMMAINV Function

6 January 2020

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the GAMMAINV function.

 

The GAMMAINV function

The Gamma distribution is widely used in engineering, science and business, to model continuous variables that are always positive and have skewed distributions.  The Gamma distribution can be useful for any variable which is always positive, such as cohesion or shear strength for example. 

It is a distribution that arises naturally in processes for which the waiting times between events are relevant, and is often thought of as a waiting time between Poisson distributed events (the Poisson distribution is a discrete probability distribution that expresses the probability of a given number of events occurring in a fixed interval).  This is what is known as “queueing analysis”.

To understand it, first think of factorials, e.g. 5! = 5 x 4 x 3 x 2 x 1 = 120.  So far, so good, but how do you calculate if the factorial number you want to evaluate isn’t an integer?  The Gamma function is used to calculate this:

Г(N+1) = N * Г(N)


That’s great (if a little recursive), so can be expressed better (!) mathematically as follows:

Clear as mud?  Well, it gets better.  The Gamma distribution just referred to has the following probability density function:

where Г(α) is the Gamma function, and the parameters α and β are both positive, i.e. α > 0 and β > 0:

  • α is known as the shape parameter, while β is referred to as the scale parameter
  • β has the effect of stretching or compressing the range of the Gamma distribution.  A Gamma distribution with β = 1 is known as the standard Gamma distribution.

The Gamma distribution represents a family of shapes.  As suggested by its name, α controls the shape of the family of distributions.  The fundamental shapes are characterized by the following values of α:

  • Case I (α < 1): when α < 1, the Gamma distribution is exponentially shaped and asymptotic to both the vertical and horizontal axes
  • Case II (α = 1): the Gamma distribution with shape parameter α = 1 and scale parameter β is the same as an exponential distribution of scale parameter (or mean) β
  • Case III (α > 1): when α is greater than one, the Gamma distribution assumes a mounded (unimodal), but skewed shape.  The skewness reduces as the value of α increases.

 

The GAMMAINV function is the inverse of the Gamma cumulative distribution, i.e. if p = GAMMADIST(x, α, β) then x = GAMMAINV(p, α, β).  This can help to identify key points in a variable whose distribution may be skewed.  It has the following syntax:

GAMMAINV(probability, alpha, beta)


It’s important to note that this function has been replaced with a new function (GAMMA.INV) that may provide improved accuracy and whose name better reflects its usage and consistency with other programming languages.  Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

The GAMMAINV function has the following arguments:

  • probability: this is required and this represents the probability associated with the distribution
  • alpha: this is also required.  This is a parameter (the shape parameter) to the distribution
  • beta: this too is  required and is another parameter (the scale parameter) to the distribution.  If beta = 1, GAMMADIST returns the standard gamma distribution.

It should be noted that:

  • if any argument is text, GAMMAINV returns the #VALUE! error value
  • if probability < 0, if probability > 1, if alpha ≤ 0 or if beta ≤ 0, GAMMAINV returns the #NUM! error value.

Given a value for probability, GAMMAINV seeks that value x such that GAMMADIST(x, alpha, beta, TRUE) = probability.  Thus, the precision of GAMMAINV depends upon the precision of GAMMADIST.  Therefore, GAMMAINV uses an iterative search technique.  If the search has not converged after 64 iterations, the function returns the #N/A error value.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here

Newsletter