Excel Statistical Functions: NORMSINV

The purposes of this article are as follows:

• To describe the NORMSINV function in Microsoft Office Excel 2003 and in later versions of Excel
• To illustrate how the function is used
• To compare results of the function for Excel 2003 and later versions of Excel with results of the function when it is used in earlier versions of Excel

NORMSINV(p) returns the value z such that, with probability p, a standard normal random variable takes on a value that is less than or equal to z. A standard normal random variable has mean 0 and standard deviation 1 (and also variance 1 because variance = standard deviation squared).

Syntax

where p is a numeric value. Because p corresponds to a probability, it must be greater than 0 and less than 1.

Example of usage

NORMSINV and NORMSDIST are related functions. If NORMSDIST(z) returns p, then NORMSINV(p) returns z.

Create a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:C24 in your worksheet.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, 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.

You may want to format columns B and C for consistent readability (such as Numbers with 5 decimal places).

Cells A1:B11 give a "mini-Normal table" similar to what you might have seen in a statistics text except that such tables contain rows for many values of z between those in A2:A11 and higher than the value 2.5 in A11.

Cells A13:B24 illustrate the use of NORMSINV. Because 0.5 in cell A14 appears in cell B3, it follows that the appropriate z value that yields NORMSDIST = 0.5 is 0 and NORMSINV(0.5) returns 0. In cell B15, you want that value of z where NORMSDIST(z) = 0.6. Entries in A4:B5 indicate that the appropriate value of z must be between 0.2 and 0.4. It must be greater than 0.2 because NORMSDIST(0.2) is less than 0.6 and it must be less than 0.4 because NORMSDIST(0.4) is greater than 0.6. The computation of NORMSINV in B15 yields the value 0.25335, this is indeed greater than 0.2 and less than 0.4. Analogously, NORMSINV(0.9) in B16 must be greater than 1 and less than 1.5 as revealed by entries in A8:B9; and the answer, 1.28155, is indeed within this range. Also, NORMSINV(0.95) in B17 must be greater than 1.5 and less than 2.0 as revealed by entries in A9:B10; and the answer, 1.644485, is within this range. Finally, NORMSINV(0.975) must also be between 1 and 1.5 according to A10:B11. Because .975 is much closer to .977 than to .933, you expect NORMSINV(0.975) to be much closer to 2 than to 1.5; and it is at 1.965996.

As an aside, past users of statistical tables for statistical hypothesis testing and computation of confidence intervals might recognize the values in A17:B18. Probability 0.05 lies in the right tail above 1.644485 because NORMSDIST(1.644485) = .95 and probability 0.025 lies in the right tail above 1.965996 because NORMSDIST(1.965996) = .975. These cutoff values are frequently used for one-tailed and two-tailed hypothesis tests respectively when the probability of rejecting the null hypothesis if true is set at 0.05.

Values in C3:C11 verify the reciprocal relationship between a function and its inverse, in this case between NORMSDIST and NORMSINV. It should be the case that z = NORMSINV(NORMSDIST(z)). If you re-format these entries to show many more decimal places, you might notice that the result is not exact because of imprecision of NORMSDIST, NORMSINV or both. However, errors appear only after a large enough number of decimal places that they are unlikely to be of concern to a user. Results in Excel 2003 and in later versions of Excel will be improved over those in Microsoft Excel 2002. Results in Excel 2002 will be improved over earlier versions.

A19:C24 show values of NORMSINV(p) for your current version of Excel for increasingly smaller values of p. Entries in column C are taken from Table 5 in Knusel, L. On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.

Results in earlier versions of Excel

The accuracy of the NORMSINV function depends on two factors. Because the calculation of the NORMSINV function uses a systematic search over the returned values of the NORMSDIST function, the accuracy of the NORMSDIST function 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. Also, the table must contain so many entries that you can find the appropriate row of the table that yields a probability that is correct to a specific number of decimal places.

Of course, by using a computer program, you do not have to build and store such a large table. Instead, individual entries are computed on demand as the search through the "table" proceeds. However, the table must be accurate and the search must continue far enough that it does not stop prematurely at an answer that has a corresponding probability (or row of the table) that is too far from the value of p that you use in the call to NORMSINV(p). Therefore, the NORMSINV function has been improved in the following ways:

• The accuracy of the NORMSDIST function has been improved.
• The search process has been improved to increase refinement.

The NORMSDIST function has been improved in Excel 2003 and in later versions of Excel. Improved refinements in the search process were introduced in Excel 2002. An article by Knusel (see note 2) discusses numeric deficiencies in the NORMSINV function in Microsoft Excel 97. These deficiencies persisted as documented by Knusel until the improvements in the 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 and in later versions of Excel

The procedure for calculating the NORMSINV function in Excel 2003 and in later versions of Excel takes advantage of improvements in the NORMSDIST function in Excel 2003 and in later versions of Excel.

For more information, click the following article number to view the article in the Microsoft Knowledge Base: Results should always agree with Knusel's to the number of decimal places shown.

Conclusions

Typically, inaccuracies in earlier versions of Excel occur for extremely small or extremely large values of p in NORMSINV(p). The values in Excel 2003 and in later versions of Excel are much more accurate.

The article about the NORMSDIST function mentions that most users are not likely to be affected by the inaccuracies in the NORMSDIST function that appear in earlier versions of Excel. Therefore, users of Excel 2002 are not likely to be affected by inaccuracies in the NORMSINV function because the search process refinements were added to Excel 2002. However, for users of earlier versions of Excel (before Excel 2002), there is more concern about the inaccuracy of the NORMSINV function because both the NORMSDIST function and the search process needed improvement in those earlier versions.