Microsoft Office Tutorials and References

In Depth Information

**Creating a frequency distribution**

Creating a frequency distribution

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

range. For example, an instructor may create a frequency distribution of grades. 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 PivotTable.

■

The FREQUENCY function

Using the
FREQUENCY
function to create a frequency distribution can be a bit tricky, and

this is probably the most difﬁ cult way to create a frequency distribution. The
FREQUENCY

function always returns an array, so you must use it in an array formula that’s entered into

a multicell range.

Figure 17.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. The goal is to count the number of values that fall into each bin.

FIGURE 17.5

Creating a frequency distribution for the data in A1:E25