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.
Search JabSto ::

Custom Search