Microsoft Office Tutorials and References

In Depth Information

Figure 10-6:
Using formulas to calculate a normal distribution and a cumulative normal distribution.

The formula in cell C5 is the same, except for the last argument. When that argument is TRUE, the function re-

turns the cumulative probability.

=NORMDIST(A5,Mean,SD,TRUE)

Figure 10-7 shows a worksheet with 2,600 data points in column A (named
Data
) that is approximately nor-

mally distributed. Formulas in column D calculate some basic statistics for this data: N (the number of data

points), the minimum, the maximum, the mean, and the standard deviation.

Column F contains an array formula that creates 25 equal-interval bins that cover the complete range of the

data. It uses the technique described in Chapter 7. The multicell array formula, entered in F2:F26 (named
Bins
)

is

=MIN(Data)+(ROW(INDIRECT(“1:25”))*(MAX(Data)-MIN(Data)+1)/25)-1

Column G also contains a multicell array formula that calculates the frequency for each of the 25 bins:

=FREQUENCY(Data,Bins)

The data in column G is displayed in the chart as columns, and uses the axis on the left.

Column H contains formulas that calculate the value of the theoretical normal distribution, using the mean and

standard deviation of the
Data
range.