Example of usageCreate a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the following table fills cells A1:D11 in your worksheet.
|-0.2||=NORMSDIST(A5)||Excel 2002 and earlier versions of Excel||Excel 2003 and later versions of Excel|
- 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
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.
Results in earlier versions of ExcelAccuracy of NORMSDIST has been improved in Excel 2003 and in later versions of Excel. 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 and in later versions of ExcelThe procedure in Excel 2003 and in later versions of Excel 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 later versions of Excel are illustrated in the worksheet in rows 6 through 11. The probabilities for Excel 2003 and for later versions of Excel 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:
ConclusionsThere are rare occasions where you might demand accuracy better than 7 decimal places. On such occasions, the version of NORMSDIST for Excel 2003 and for later versions of Excel will give superior performance. For all other computations that involve NORMSDIST, you should not notice a difference between the earlier versions of Excel and the later 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 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.
Artikel-id: 827369 – senaste granskning 19 sep. 2011 – revision: 1