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.
GAMMAINV(p, alpha, beta)
Example of usageTo 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.
|number of phases||4|
- 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 Formatmenu, and then click AutoFit Selection.
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 ExcelGAMMAINV(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"
Results in Excel 2003 and in later versions of ExcelNo 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).
ConclusionsMany 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).
Article ID: 828334 - Last Review: Apr 29, 2008 - Revision: 1