Article ID: 827358  View products that this article applies to. On This PageSUMMARYThe purpose of this article is to describe the NORMINV
function in Microsoft Office Excel 2003 and in later versions of Excel, to illustrate how the function is used, and to compare
results of the function for Excel 2003 and in later versions of Excel with results of NORMINV when it is used in
earlier versions of Excel. MORE INFORMATIONNORMINV (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. SyntaxNORMINV(p, mu, sigma) 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 usageNORMINV 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((xmu)/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. Collapse this table
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 ExcelAccuracy 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:
Note 2 Knusel, L. On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375377, 1998. Results in Excel 2003 and in later versions of ExcelThe 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 Results should always agree with Knusel's to the
number of decimal places that are shown.
(http://support.microsoft.com/kb/827369/
)
Excel statistical functions:
NORMSDIST
ConclusionsInaccuracies 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. PropertiesArticle ID: 827358  Last Review: September 19, 2011  Revision: 3.0
