Microsoft Office Tutorials and References

In Depth Information

**Plotting a normal curve**

Figure 8-43: The XY charts display the normal distribution and the

cumulative normal distribution.

Column A contains formulas that generate values ranging from –3 SD units to

+3 SD units. The formula in cell A5, which was copied to the cells below, is

=(-SD*3)+Mean

Column B contains formulas that generate the height of the curve for a given

mean and standard deviation. The formula in cell B5 is

=NORMDIST(A5,Mean,SD,FALSE)

The formulas in column C also use the NORMDIST function, but the fourth

argument is set to TRUE. The formula in C5 is

=NORMDIST(A5,Mean,SD,TRUE)

In some cases, you may want to compare a histogram created from your data with

the theoretical normal distribution. Figure 8-44 shows an example of how this can be

done. The chart is a combination with two value axes. The data consist of 2,600 data

points (not shown). Simple formulas in column D calculate key statistics for the data.

The histogram is generated from the data in column G, using the FREQUENCY

function (see the previous section). The normal distribution curve uses the data in

column H. The formula in cell H2, which is copied to the cells below, is

=NORMDIST(F2,$D$4,$D$5,FALSE)