MORE INFORMATION
NORMSDIST(z) returns the probability that the observed value
of a standard normal random variable will be less than or equal to z. A
standard normal random variable has mean 0 and standard deviation 1 (and also
variance 1 because variance = standard deviation squared).
Syntax
NORMSDIST(z)
where z is a numeric value.
Example Usage
Create a blank Excel worksheet, copy the table below, select cell
A1 in your blank Excel worksheet, and then click
Paste on the
Edit menu so that the entries in the table below fill cells
A1:D11 in your worksheet.
| z | NORMSDIST(z) | | |
| | | |
| 0 | =NORMSDIST(A3) | | |
| 0.2 | =NORMSDIST(A4) | =1 -B4 | |
| -0.2 | =NORMSDIST(A5) | pre-Excel
2003 | Excel 2003 |
| -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 |
Note 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, point to
Column on the
Format menu, and then click
AutoFit Selection. (You may want to format cells B6:D11
consistently, such as Scientific with 5 decimal places.)
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 pre-Excel 2003
are shown in Column C and values for Excel 2003 are in Column D. In every case,
the Excel 2003 value is more accurate. However, for some values of z, the
difference in NORMSDIST values between earlier versions of Excel and Excel 2003
are 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.
Results in Earlier Versions of Excel
Accuracy of NORMSDIST has been improved in Excel 2003. In earlier
versions of Excel, a single computational procedure was used for all values of
z. Results were essentially accurate to 7 decimal places, more than sufficient
for most practical examples.
Results in Excel 2003
The procedure in Excel 2003 uses two different computational
procedures depending on the value of z. The first is for z between -5 and +5;
the second is for z values in the extreme left or right tails, below -5 or
above +5. Accuracy was improved for all values since, over the range of z
values where each was used, these two methods were both superior to the single
method used in earlier versions of Excel. Typical accuracy is now 14 to 15
decimal places. Differences in NORMSDIST between earlier versions of Excel and
Excel 2003 are illustrated in the worksheet in rows 6 through 11. The Excel
2003 probabilities are accurate to as many decimal places as shown when
compared with tables in Abromowitz, M. and I.A. Stegun,
Handbook of
Mathematical Functions, Dover, New York, 1972 or with results in Knusel, L.
On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.
Knusel's statistical software, ELV (see note 1), was used both in Knusel's
paper and in our analysis for comparison purposes.
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.
Conclusions
There are rare occasions where you might demand accuracy better
than 7 decimal places. On such occasions, the Excel 2003 version of NORMSDIST
will give superior performance; for all other computations involving NORMSDIST,
you should not notice a difference between Excel 2003 and earlier versions of
Excel.
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, NORMSDIST has
also been improved so that NORMSINV's performance is better than in Excel 2002
and better yet than in versions before Excel 2002.
NORMDIST and
NORMINV also rely on accuracy of NORMSDIST and have been improved as a result
of improvements in NORMSDIST.
For more information
about NORMSDIST, click
Microsoft Office Excel Help on the
Help menu, type
normsdist in the
Search for box in the Assistance pane, and then click
Start searching to view the topic.