You are currently offline, waiting for your internet to reconnect

`NORMSDIST(z)`

z | NORMSDIST(z) | ||

0 | =NORMSDIST(A3) | ||

0.2 | =NORMSDIST(A4) | =1 -B4 | |

-0.2 | =NORMSDIST(A5) | Excel 2002 and earlier versions of Excel | Excel 2003 and later versions of Excel |

-1 | =NORMSDIST(A6) | 1.58655E-01 | 1.58655E-01 |

-2 | =NORMSDIST(A7) | 2.27501E-02 | 2.27501E-02 |

-3 | =NORMSDIST(A8) | 1.34997E-03 | 1.34990E-03 |

-4 | =NORMSDIST(A9) | 3.16860E-05 | 3.16712E-05 |

-5 | =NORMSDIST(A10) | 2.87105E-07 | 2.86652E-07 |

-7 | =NORMSDIST(A11) | 1.28808E-12 | 1.27981E-12 |

- In Microsoft Office Excel 2007, click the
**Home**tab, click**Format**in the**Cells**group, and then click**AutoFit Column Width**. - In Microsoft Office Excel 2003, point to
**Column**on the**Format**menu, and then click**AutoFit Selection**.

The standard normal distribution is a continuous probability distribution. Probability is distributed according to the familiar "bell-shaped" curve with total area under the curve equal to 1. The probability of a value less than or equal to z occurring is just the area under this curve to the left of z.

In cell B3, z=0 and NORMSDIST(0) = 0.5. The standard normal distribution is centered at 0 and has half of its probability to the left of 0 and half to the right.

Cells A5:A11 show negative z values. Column B shows NORMSDIST values for your current version of Excel. NORMSDIST values for Excel 2002 and for earlier versions of Excel are shown in Column C. Values for Excel 2003 and for later versions of Excel are in Column D. In every case, the value for Excel 2003 and for later versions of Excel is more accurate. However, for some values of z, the difference in NORMSDIST values between earlier versions of Excel and later versions of Excel is negligible.

Notice that values of NORMSDIST(z) in cells B5:B11 (or C5:C11 or D5:D11) become smaller as z becomes more negative and further from 0. Using the correct values in column D, the chance of observing a value less than or equal to -3 is 0.00135, just over one chance in 1,000; the chance of observing a value less than or equal to -4 is 3.1671E-05, about one chance in 31,500; the chance of observing a value less than or equal to -5 is 2.867E-07 or about one chance in 3.5 million.

The "bell-shaped" curve is symmetrical about 0; the part of the curve to the right of zero is a "mirror image" of the part of the curve to the left of 0. The area to the left of –z is the same as the area to the right of z. Therefore, the chances of observing a value greater than 3, 4, or 5 are also 0.00135, 3.1671E-05, and 2.867E-07 respectively. This symmetry is illustrated in cells A4:C5. Cell B4 gives the probability of an observed value less than or equal to 0.2. The probability of a value greater than 0.2 is 1 minus this quantity and is shown in cell C4. Notice that the value in C4 is equal to the value in B5, illustrating the fact that the probability of a value less than -0.2 (in cell B5) and the probability of a value greater than +0.2 (in cell C4) are the same. Values below a negative number are said to lie in the left tail of the distribution. Values above a positive number are said to lie in the right tail.

Note 1 To view Professor Knusel's homepage, visit the following University of Munich Web site:Comparing entries in C6:D11, one can see that relative differences (for example, the ratios C6/D6, C7/D7, ..., C11/D11) become larger as z becomes further from 0. However, absolute differences (C6-D6, C7-D7, ..., C11-D11) become smaller. All entries in C10:D11 are very small, all less than 3 chances in 10 million.

Improving NORMSDIST also improves functions that use it, in particular, NORMSINV. NORMSINV(p) returns that value z such that NORMSDIST(z) = p. Accuracy of NORMSINV depends on accuracy of NORMSDIST and the quality of the search procedure in its ability to "home in on" the appropriate value of z that corresponds to the user-supplied p. For Excel 2002, the search procedure was improved, but changes were not made in NORMSDIST. For Excel 2003 and for later versions of Excel, NORMSDIST has also been improved so that NORMSINV's performance is better than in Excel 2002 and better yet than in versions of Excel that are earlier than Excel 2002.

NORMDIST and NORMINV also rely on the accuracy of NORMSDIST. NORMDIST and MORMINV were improved because of improvements in NORMSDIST.

Properties

Article ID: 827369 - Last Review: 09/19/2011 00:04:00 - Revision: 4.0

Microsoft Office Excel 2007

- kbexpertisebeginner kbfunctions kbfuncstat kbinfo KB827369