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.
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 )
Column G also contains a multicell array formula that calculates the frequency for each of the 25 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.