- 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
Example of usageNORMSINV 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.
- 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
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 ExcelThe 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.
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 ExcelThe 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:
ConclusionsTypically, 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.