Excel statistical functions: NORMINV

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 of 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).

To illustrate the NORMINV function, create a blank Excel worksheet, and copy the following table. Select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:D14 in your worksheet.

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, 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 occurs 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 the following improvements:

• Improvements in the accuracy of NORMSDIST

• Improvements in the search process for increased refinement

NORMSDIST has been improved, but only for Excel 2003 and for later versions of Excel, not for earlier versions of Excel. Improved refinements in the search process were introduced in Microsoft Excel 2002, but not before that. An article by Knusel (see note 2) pointed out numeric deficiencies in NORMSINV in Microsoft Excel 97. These deficiencies persisted, as documented by Knusel, until the improvements in search process in Excel 2002 made results better. However, the results were 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 NORMSINV in Excel 2003 and in later versions of Excel takes advantage of improvements in NORMSDIST for Excel 2003 and for later versions of Excel.

For more 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). Values in Excel 2003 and in later versions of Excel 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 of Excel. From this, 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 and earlier versions of Excel, there is more concern about inaccuracy of NORMSINV because both NORMSDIST and the search process needed improvement in those versions.