Excel statistical functions: GAMMAINV

This article describes the GAMMAINV function in Microsoft Office Excel 2003 and in later versions of Excel, and discusses an improvement in Excel 2003 and in later versions of Excel. The improvement could affect results in extreme cases when compared with earlier versions of Excel.

The GAMMAINV(p, alpha, beta) function is the inverse function for the GAMMADIST(x, alpha, beta, TRUE) function. The last argument to GAMMADIST indicates whether the cumulative distribution function is wanted; for using GAMMADIST in the evaluation of GAMMAINV, the cumulative = TRUE case is wanted. For any particular x, GAMMADIST(x, alpha, beta, TRUE) returns the probability that a GAMMA-distributed random variable with parameters alpha and beta is less than or equal to x.

The GAMMAINV(p, alpha, beta) function returns the value x for which GAMMADIST(x, alpha, beta, TRUE) returns p. Therefore, GAMMAINV is evaluated by a search process that returns the appropriate value of x by evaluating GAMMADIST for various candidate values of x until it finds a value of x for which GAMMADIST(x, alpha, beta, TRUE) is "acceptably close" to p.

Special cases of the gamma distribution include the exponential distribution, when alpha equals 1, and the Erlang distribution, when alpha is a positive integer greater than 1. Additionally, when n is a positive integer, GAMMADIST(x, n/2, 2, TRUE) returns 1 – CHIDIST(x, n). Therefore the Chi-square distributions (for any number of degrees of freedom), the exponential distributions, and the Erlang distributions are all special cases of the family of gamma distributions.

Syntax

Note p is a probability with 0 < p < 1; alpha and beta are positive numeric parameters of the gamma distribution. (They specify which distribution in the gamma family you want, just as the mean and standard deviation specify which member of the normal family you want when you call NORMDIST or NORMINV.)

Example of usage

To illustrate the GAMMAINV function, create a blank Excel worksheet, and then copy the following table. Select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:B21 in your worksheet. After the table is pasted in the Excel worksheet, click the Paste Options button, and then click Match Destination Formatting. With the pasted range still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:

• In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
• In Excel 2003, point to Column on the Format menu, and then click AutoFit Selection.

An assembly process is made up of four stages. The duration of each stage has a mean of 10 minutes; the actual length of each stage follows an exponential distribution. The sum of the durations of the four stages has an Erlang(4) distribution where the parameter, 4, is the number of independent exponential random variables summed to create the Erlang. You can investigate the exponential and Erlang distributions through GAMMADIST and GAMMAINV.

Cell A10 gives the cumulative probability that an exponential random variable with mean 10 takes on a value less than or equal to its mean. This is 0.632. Actually, this same value applies to the probability that any exponential random variable is less than or equal to its mean. The value 0.632 is much higher than 0.5 because the exponential distribution has a "heavy right tail;" that is, the probability distribution is skewed to the right. Although values less than the mean cannot go below 0, it is not uncommon to obtain values more than two or three times the mean. Cell A11 verifies the inverse relationship between GAMMADIST and GAMMAINV. Cell A12 gives the cumulative probability of an observed value less than or equal to 15, 1.5 times the mean. Cell A13 gives the cutoff point below which probability 0.95 falls. Values higher than 29.96 (3.00 times the mean) will occur 5 percent of the time.

Cells A15:A17 give results for the Erlang(4) distribution. Cell A15 gives the cumulative probability that an Erlang(4) random variable is less than or equal to its mean. This cumulative probability is 0.567, which is lower than the corresponding value for the exponential. It is lower because the Erlang(4) distribution is less skewed. Cell A16 gives the cumulative probability of a value less than or equal to 1.5 times the mean. This is larger than the corresponding value in A12. Cell A17 gives the cutoff point below which probability 0.95 falls. Values higher than 77.53 (1.94 times the mean) will occur 5 percent of the time.

Cells A19:A21 parallel cells A15:A17 for an Erlang random variable with 10 phases, each of which is an exponential random variable with mean 10. The probability of a value less than or equal to the mean is 0.542. The probability of a value less than or equal to 1.5 times the mean is 0.930; the 0.95 cutoff is at 157.05 so that values higher than this (1.57 times the mean) will occur 5 percent of the time. With 10 phases, this Erlang distribution looks a little closer to a normal distribution. It is even less skewed than the Erlang(4).

Results in earlier versions of Excel

GAMMAINV(p, alpha, beta) is found through an iterative process that repeatedly evaluates GAMMADIST(x, alpha, beta, TRUE) and returns a value of x such that GAMMADIST(x, alpha, beta, TRUE) is "acceptably close" to p. Therefore, the accuracy of GAMMAINV depends on the following factors:

• The accuracy of GAMMADIST
• The design of the search process and definition of "acceptably close"

In rare cases, "acceptably close" in earlier versions of Excel might not be close enough. This is unlikely to affect most users. Basically, if you request GAMMAINV(p, alpha, beta), the search would continue until a value of x was found for which GAMMADIST(x, alpha, beta, TRUE) differed from p by less than 0.0000003.

Results in Excel 2003 and in later versions of Excel

No changes to GAMMADIST were made in Excel 2003 and in later versions of Excel. The only change affecting GAMMAINV was to redefine "acceptably close" in the search process to be much closer. The search now continues until the closest possible value of x is found (within the limits of the finite precision arithmetic of Excel). The resulting x should have a GAMMADIST(x, alpha, beta, TRUE) value that differs from p by about 10^(-15).

Conclusions

Many inverse functions have been improved for Excel 2003 and for later versions of Excel. Some have been improved for Excel 2003 and for later versions of Excel only by continuing the search process to reach a higher level of refinement.

Included in this set of inverse functions are BETAINV, CHIINV, FINV, GAMMAINV, and TINV. No modifications were made to the respective functions called by these inverse functions: BETADIST, CHIDIST, FDIST, GAMMADIST, and TDIST.

Additionally, this same improvement in the search process was made for NORMSINV in Microsoft Excel 2002. For Excel 2003 and for later versions of Excel, accuracy of NORMSDIST (called by NORMSINV) was improved also. These changes also affect NORMINV and LOGINV (which call NORMSINV) and NORMDIST and LOGNORMDIST (which call NORMSDIST).