Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
Figure 7-6 shows some data in range A1:E25 (named Data ). These values range from 1 to 500.
The range G2:G11 contains the bins used for the frequency distribution. Each cell in this bin range
contains the upper limit for the bin. In this case, the bins consist of <=50, 51–100, 101–150, and so
on. See the sidebar, “Creating bins for a frequency distribution,” to discover an easy way to
create a bin range.
Figure 7-6: Creating a frequency distribution for the data in A1:E25.
To create the frequency distribution, select a range of cells that corresponds to the number of
cells in the bin range. Then enter the following array formula:
The array formula enters the count of values in the Data range that fall into each bin. To create a
frequency distribution that consists of percentages, use the following array formula:
Figure 7-7 shows two frequency distributions — one in terms of counts, and one in terms of
percentages. The figure also shows a chart (histogram) created from the frequency distribution.