Description of the NORMDIST function in Excel 2003 (827371)

MORE INFORMATION

Because NORMDIST and NORMSDIST are closely related, Microsoft recommends that you first become familiar with NORMSDIST. NORMSDIST is referred to in this article for comparison purposes.

For additional information about NORMSDIST, click the following article number to view the article in the Microsoft Knowledge Base: NORMDIST (x, mu, sigma, cumulative) is most generally used with its last argument set to TRUE. (Excel interprets 1 as TRUE and 0 as FALSE.)

Syntax

The NORMDIST parameters, x, mu and sigma, are numeric values, where the parameter, cumulative, is a logical TRUE or FALSE value. Sigma must be greater than 0, but there is no similar requirement for x or mu.

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 Usage

The 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 Microsoft Excel when the cumulative for NORMDIST is set to FALSE because NORMSDIST does not have an equivalent option.

To illustrate the differences between NORMDIST and NORMSDIST, follow these steps:

1. Create a blank Microsoft Excel worksheet, and then copy the following table:

   x	mu	sigma	(x - mu)/sigma	NORMDIST(x,mu,sigma,TRUE)	NORMSDIST((x - mu)/sigma)
   100	100	15	=(A3-B3)/C3	=NORMDIST(A3,B3,C3,TRUE)	=NORMSDIST(D3)
   90	100	15	=(A4-B4)/C4	=NORMDIST(A4,B4,C4,TRUE)	=NORMSDIST(D4)
   70	100	15	=(A5-B5)/C5	=NORMDIST(A5,B5,C5,TRUE)	=NORMSDIST(D5)
   130	100	15	=(A6-B6)/C6	=NORMDIST(A6,B6,C6,TRUE)	=NORMSDIST(D6)

2. In your blank Microsoft Excel worksheet, select cell A1.
3. Click Paste on the Edit menu so that the entries in the table fill cells A1:F6 in your worksheet.

The Normal distribution is a continuous probability distribution whose shape is determined by its mean, mu, and standard deviation, 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 100 and standard deviation 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.

BecauseIt follows thatThe expression on the left of this last equation is the probability of an observation below 70 (or lower than two standard deviations below the mean); the expression on the right is the probability of an observation greater than 130 (or higher than two standard deviations greater than the mean).

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 Excel

The accuracy of NORMSDIST and NORMDIST has been improved in Excel 2003. 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; results that are more than sufficient for most practical examples.

Results in Excel 2003

The computational procedure for NORMSDIST in Excel 2003 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.

Conclusions

Rarely, you may require results to an accuracy that are greater than seven decimal places. As is demonstrated, both NORMDIST and NORMSDIST in Excel 2003 can provide these results. But, for all other computations that involve NORMDIST and NORMSDIST, you generally do not see a difference between Excel 2003 and earlier versions.

Functions that use NORMSDIST are also improved in Excel 2003, such as the NORMSINV function. 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 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. For Excel 2003, 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 earlier.

The NORMINV function also relies on the accuracy of NORMSINV, and it has been improved as a result of improvements in NORMSDIST and NORMSINV.

For more information about NORMDIST, click Microsoft Office Excel Help on the Help menu, type normdist in the Search for box in the Assistance pane, and then click Start searching to view the topic.