Microsoft Office Tutorials and References

In Depth Information

This formula has a serious limitation: If the range contains any blank cells, it returns an error. The following ar-

ray formula solves this problem:

{=SUM(IF(COUNTIF(Data,Data)=0,””,1/COUNTIF(Data,Data)))}

To create an array formula that returns a list of unique items in a range, see Chapter 15.

Creating a frequency distribution

A
frequency distribution
basically comprises a summary table that shows the frequency of each value in a

range. For example, an instructor may create a frequency distribution of test scores. The table would show the

count of As, Bs, Cs, and so on. Excel provides a number of ways to create frequency distributions. You can

• Use the FREQUENCY function.

• Create your own formulas.

• Use the Analysis ToolPak add-in.

• Use a pivot table.

The frequency distribution.xlsx workbook that demonstrates these four techniques is

available at this book's website.

The FREQUENCY function

The first method that I discuss uses the FREQUENCY function. This function always returns an array, so you

must use it in an array formula entered into a multicell range.

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.

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 dis-

tribution that consists of percentages, use the following array formula:

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