MORE INFORMATION
NORMINV (p, mu, sigma) returns the value x such that, with
probability p, a normal random variable with mean mu and standard deviation
sigma takes on a value less than or equal to x.
Syntax
NORMINV(p, mu, sigma)
where p, mu, and sigma are numeric values. Because p corresponds
to a probability (see note 1), it must be greater than 0 and less than 1.
Because sigma represents standard deviation it must be greater than
0.
Note 1 More precisely, "since p corresponds to a cumulative probability
for a continuous random variable that can take on any value between minus
infinity and plus infinity... ." There is no specific negative z for
NORMSDIST(z) = 0 and no specific positive z for NORMSDIST(z) = 1 (although
there are values of z arbitrarily close to 0 and 1 respectively), thus we must
insist that for NORMINV(p), 0 < p < 1 rather than 0 <= p <= 1.
Example Usage
NORMINV and NORMDIST are related functions. If NORMDIST(x, mu,
sigma) returns p, then NORMINV(p, mu, sigma) returns x. Similarly, NORMSINV and
NORMSDIST are related functions; if NORMSDIST(z) returns p, then NORMSINV(p)
returns z. Excel essentially converts NORMDIST(x, mu, sigma) to mu +
sigma*NORMSDIST((x-mu)/sigma). NORMINV(p, mu, sigma) is similarly converted to
mu + sigma*NORMSINV(p). Create a blank Excel worksheet, copy the table below,
select cell A1 in your blank Excel worksheet, and then click
Paste on the
Edit menu so that the entries in
the table below fill cells A1:D14 in your worksheet.
| mu | 100 | | |
| sigma | 10 | | |
| p | NORMINV(p, mu,
sigma) | NORMSINV(p) | mu + sigma*NORMSINV(p) |
| 0.5 | =NORMINV(A4, $B$1,
$B$2) | =NORMSINV(A4) | =$B$1 + $B$2*C4 |
| 0.6 | =NORMINV(A5, $B$1,
$B$2) | =NORMSINV(A5) | =$B$1 + $B$2*C5 |
| 0.9 | =NORMINV(A6, $B$1,
$B$2) | =NORMSINV(A6) | =$B$1 + $B$2*C6 |
| 0.95 | =NORMINV(A7, $B$1,
$B$2) | =NORMSINV(A7) | =$B$1 + $B$2*C7 |
| 0.975 | =NORMINV(A8, $B$1,
$B$2) | =NORMSINV(A8) | =$B$1 + $B$2*C8 |
| 0.001 | =NORMINV(A9, $B$1,
$B$2) | =NORMSINV(A9) | =$B$1 + $B$2*C9 |
| 0.0001 | =NORMINV(A10, $B$1,
$B$2) | =NORMSINV(A10) | =$B$1 + $B$2*C10 |
| 0.00001 | =NORMINV(A11, $B$1,
$B$2) | =NORMSINV(A11) | =$B$1 + $B$2*C11 |
| 0.000001 | =NORMINV(A12, $B$1,
$B$2) | =NORMSINV(A12) | =$B$1 + $B$2*C12 |
| 0.0000003 | =NORMINV(A13, $B$1,
$B$2) | =NORMSINV(A13) | =$B$1 + $B$2*C13 |
| 0.0000002 | =NORMINV(A14, $B$1,
$B$2) | =NORMSINV(A14) | =$B$1 + $B$2*C14 |
Note After you paste this table into your new Excel 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. You may want to format columns B, C, and D
for consistent readability (such as Numbers with 5 decimal places).
Cells A4:B14 show values of NORMINV(p, mu, sigma) for specific values
of p. Mu and sigma are initialized to 100 and 10, in cells B1 and B2. Comparing
values in columns B and D shows that NORMINV(p, mu, sigma) is equal to mu +
sigma*NORMSINV(p). As mentioned earlier, if you call NORMINV(p, mu, sigma),
Excel computes mu + sigma*NORMSINV(p). Any inaccuracy in NORMINV is because of
an inaccuracy in NORMSINV.
The three sections below discuss
inaccuracies in NORMSINV and are taken directly from the article on NORMSINV.
That article also has a different Excel example and a tutorial discussion of
the relationship between a function and its inverse.
You can
experiment with changing values of mu and sigma in cells B1 and B2. Change mu
to 200 and notice that this just adds 100 to all NORMINV results. Change mu
back to 100 and change sigma to 20. Notice that because sigma has doubled,
values returned by NORMINV are two times as far from mu (for example, 100) as
before. Of course, you can investigate the effects of more changes in mu and
sigma on your own. Increasing mu increases all values of NORMSINV by the same
amount; increasing sigma by a multiple greater than 1 while leaving mu
unchanged, increases the distance of all values of NORMSINV from mu by the same
multiple.
Results in Earlier Versions of Excel
Accuracy of NORMSINV depends on two factors. Because NORMSINV
involves a systematic search over values of NORMSDIST, accuracy of NORMSDIST is
critical.
Also, the search must be sufficiently refined that it
"homes in" on an appropriate answer. To use the textbook Normal probability
distribution table as an analogy, entries in the table must be accurate and so
many that you can find the appropriate row of the table that yields a
probability correct to a specific number of decimal places. Of course, with
computer software one does not build and store such a monstrous table; instead,
individual entries are computed on demand as the search through the "table"
proceeds. But, the table has to be accurate in the first place and the search
has to continue far enough that it does not stop prematurely with an answer
whose corresponding probability (row of the table, if you like) is too far from
the user's p in the call to NORMSINV(p). Therefore, improvements in NORMSINV
are made up of
- improvements in the accuracy of NORMSDIST;
- improvements in the search process for increased
refinement.
NORMSDIST has been improved but only for Excel 2003, not for
earlier versions of Excel. Improved refinements in the search process were
introduced in Excel 2002 but not before that. An article by Knusel (see note 2)
pointed out numeric deficiencies in NORMSINV in Excel 97. These deficiencies
persisted as documented by Knusel until the improvements in search process in
Excel 2002 made results better, but still not in complete agreement with
Knusel's.
Note 2 Knusel, L.
On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.
Results in Excel 2003
The procedure for NORMSINV in Excel 2003 takes advantage of
improvements in NORMSDIST for Excel 2003.
For additional information, click the following article
number to view the article in the Microsoft Knowledge Base:
827369
Excel Statistical Functions: NORMSDIST
Results should always agree with Knusel's to the
number of decimal places that are shown.
Conclusions
Inaccuracies in earlier versions of Excel typically occur for
extremely small or extremely large values of p in NORMSINV(p). Excel 2003
values are much more accurate. The article about NORMSDIST points out that most
users are not likely to be affected by inaccuracies in NORMSDIST in earlier
versions. From this comment it follows that users of Excel 2002 are not likely
to be affected by inaccuracies in NORMSINV because the search process
refinements were added to Excel 2002. For users of Excel 2002 v and earlier,
there is more concern over inaccuracy of NORMSINV because both NORMSDIST and
the search process needed improvement in those versions.
For more information
about NORMINV, click
Microsoft Office Excel Help on the
Help menu, type
norminv in the
Search for box in the Assistance pane, and then click
Start searching to view the topic.