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:

{=FREQUENCY(Data,G2:G11)}

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:

{=FREQUENCY(Data,G2:G11)/COUNT(Data)}

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.