MORE INFORMATION
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 Use
To illustrate the GAMMAINV function, follow these steps:
- Create a blank Excel worksheet, and then copy the following
table:
| Exponential Distribution | |
| mean | 10 |
| standard deviation | =B2 |
| |
| Erlang Distribution | |
| number of phases | 4 |
| mean | =B6*B2 |
| standard deviation | =SQRT(B6)*B3 |
| |
| =GAMMADIST(10,1,10,TRUE) | |
| =GAMMAINV(A10,1,10) | |
| =GAMMADIST(15,1,10,TRUE) | |
| =GAMMAINV(0.95,1,10) | |
| |
| =GAMMADIST(40,4,10,TRUE) | |
| =GAMMADIST(60,4,10,TRUE) | |
| =GAMMAINV(0.95,4,10) | |
| |
| =GAMMADIST(100,10,10,TRUE) | |
| =GAMMADIST(150,10,10,TRUE) | |
| =GAMMAINV(0.95,10,10) | |
- Select cell A1 in your blank Excel worksheet, and then
click Paste on the Edit menu so that the
entries in the table fill cells A1:B21 in your worksheet.
- Click the Paste Options button, and then
click Match Destination Formatting. With the pasted range
still selected, 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 two 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
No changes were made in Excel 2003 to GAMMADIST. 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. Some
have been improved for Excel 2003 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 Excel 2002. For Excel 2003,
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).
For more information about GAMMAINV, click
Microsoft Excel Help on the
Help menu, type
gammainv in the
Search for box in the
Assistance pane, and then click
Start searching to view the
topic.