For more information about NORMSDIST, click the following article number to view the article in the Microsoft Knowledge Base:
NORMDIST(x, mu, sigma, cumulative)
In NORMDIST, when the last argument is set to TRUE, NORMDIST returns the cumulative probability that the observed value of a Normal random variable with mean mu and standard deviation sigma will be less than or equal to x. If cumulative is set to FALSE (or 0, interpreted as FALSE), NORMDIST returns the height of the bell-shaped probability density curve.
Example of usageThe following sample exercise illustrates the relationship between NORMDIST and NORMSDIST when NORMDIST is called and the last argument (cumulative) is set to TRUE.
Note There is no comparable relationship in Excel when the cumulative for NORMDIST is set to FALSE. This is because NORMSDIST does not have an equivalent option.
To illustrate the differences between NORMDIST and NORMSDIST, create a blank Microsoft Excel worksheet, copy the following table, select cell A1 in the worksheet, and then paste the entries so that the table fills cells A1:F6 in your worksheet.
|x||mu||sigma||(x - mu)/sigma||NORMDIST(x,mu,sigma,TRUE)||NORMSDIST((x - mu)/sigma)|
The probability is distributed according to the familiar bell-shaped curve with the total area under the curve equal to 1. The probability that a value that is less than or equal to x will occur (also called cumulative probability up to x) is the area under this curve to the left of x. (The standard normal distribution is the special case where mu = 0 and sigma = 1.)
Because NORMDIST is used only in cells E3:E6 of the Excel worksheet and in each case is used with cumulative set to TRUE, the cumulative probability up to x is returned. All examples use mu = 100 and sigma = 15. (Intelligence quotients, or IQ scores, are frequently assumed to follow a normal distribution with a mean of 100 and standard deviation of 15. Mu = 100 and sigma = 15 are the appropriate settings for this distribution.)
The normal distribution with mean mu and standard deviation sigma is centered at mu and has half of its probability to the left of mu and half to the right. Row 3 illustrates this point. Because x = mu in this example, the probability to the left of mu is 0.5 as shown in cell E3. In Row 4, x = 90, a value below mu. The probability to the left of 90 is less than 0.5 as shown in cell E4.
The values of x in cells A5 and A6 are 70 and 130, two standard deviations below the mean and two standard deviations greater than the mean, respectively (because 70 = 100 – 2*15 and 130 = 100 + 2*15). The respective values of NORMDIST in cells E5 and E6 sum to 1. These values help show the symmetry of the bell-shaped normal distribution curve.
NORMDIST(70,100,15,TRUE) + NORMDIST(130,100,15,TRUE) =
NORMDIST(70,100,15,TRUE) = 1 -
The entries in columns E and F are the same. These entries illustrate the relationship between the normal distribution with a specific mu and sigma (in this example, 100 and 15, respectively) and the standard normal distribution.
When you convert a probability question that involves any normal distribution into an equivalent question that involves the standard normal distribution, you must standardize. Standard normal distribution tables always refer only to the standard normal distribution, and standardizing must take place before you use these tables.
The computational procedure in Excel effectively standardizes also. Each call to NORMDIST in column E is converted internally in Excel to the corresponding call to NORMSDIST in column F. The value found by NORMSDIST is then returned to the user. The accuracy of NORMDIST depends on the accuracy of NORMSDIST.
Results in earlier versions of ExcelThe accuracy of NORMSDIST and NORMDIST has been improved in Excel 2003 and in later versions of Excel. In earlier versions of Excel, a single computational procedure is used for all calls to NORMSDIST (either directly or internally in Excel when the user calls NORMDIST.) The results are essentially accurate to seven decimal places. This is more than sufficient for most practical examples.
Results in Excel 2003 and in later versions of ExcelThe computational procedure for NORMSDIST in Excel 2003 and in later versions of Excel uses two different computational procedures that depend on the standardized value z, as used by NORMSDIST. If you call NORMDIST(x, mu, sigma, TRUE), the standardized value used by NORMSDIST is (x – mu)/sigma.
The first computational procedure is for z between -5 and +5; the second is for z values in the extreme left or right tails, below -5 or greater than +5. The accuracy is improved for all values because, over the range of z values where each was used, the two computational procedures result in a greater accuracy than the single method used in earlier versions of Excel. The typical accuracy is now at 14 to 15 decimal places.
ConclusionsRarely, you may require results that are more accurate than seven decimal places. As is demonstrated, both NORMDIST and NORMSDIST in Excel 2003 and in later versions of Excel can provide these results. However, for all other computations that involve NORMDIST and NORMSDIST, you generally do not see a difference between later versions of Excel and earlier versions of Excel.
Functions that use NORMSDIST, such as the NORMSINV function, are also improved in Excel 2003 and in later versions of Excel. The NORMSINV(p) function returns value z such that NORMSDIST(z) = p. The accuracy of the NORMSINV function depends on the accuracy of NORMSDIST and on the ability of the search procedure to determine the appropriate value of z that corresponds to the user-supplied p.
Note In Microsoft Excel 2002, the search procedure was improved, but changes were not made in NORMSDIST. In Excel 2003 and in later versions of Excel, NORMSDIST has also been improved so that the performance of the NORMSINV function is better than the performance of the function in Excel 2002 and in earlier versions of Excel.
The NORMINV function also relies on the accuracy of NORMSINV, and it has been improved as a result of improvements in NORMSDIST and NORMSINV.
KEYWORDS: XL2002 XL2003 XL2007 XL2010 Normdist formula kbhowto NORMSDIST NORMDIST
Artikel-id: 827371 – senaste granskning 18 sep. 2011 – revision: 1