Excel 2010 Bible

Part II: Working with Formulas and Functions

294

On the CD
The companion CD-ROM contains a workbook that demonstrates this technique. The file is named count
unique.xlsx.

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 A’s, B’s, C’s, and so on. Excel provides a number of ways to create fre-

quency distributions. You can

l (^) Use the FREQUENCY function.
l Create your own formulas.
l (^) Use the Analysis ToolPak add-in.
l Use a pivot table.
On the CD
A workbook that demonstrates these four techniques appears on the companion CD-ROM. The file is named
frequency distribution.xlsx.

The FREQUENCY function ...........................................................................

Using the FREQUENCY function to create a frequency distribution can be a bit tricky. This function

always returns an array, so you must use it in an array formula that’s entered into a multicell range.

Figure 13.5 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.

To create the frequency distribution, select a range of cells that corresponds to the number of cells

in the bin range (in this example, select H2:H11 because the bins are in G2:G11). Then enter the

following array formula into the selected range (press Ctrl+Shift+Enter it):

{=FREQUENCY(Data,G2:G11)}

The array formula returns 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 13.6 shows two frequency distributions — one in terms of counts and one in terms of per-

centages. The figure also shows a chart (histogram) created from the frequency distribution.