You are currently offline, waiting for your internet to reconnect

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.

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.

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 |

- 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**.

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.

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

Note 2 Knusel, L. On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.

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

Properties

Article ID: 827358 - Last Review: 09/19/2011 00:05:00 - Revision: 3.0

Microsoft Office Excel 2007

- kbexpertisebeginner kbformula kbinfo KB827358