Microsoft Office Tutorials and References

In Depth Information

**Creating a frequency distribution**

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 17.6 shows two frequency distributions — one in terms of counts and one in terms

of percentages. The ﬁ gure also shows a chart (histogram) created from the frequency

distribution.

17

FIGURE 17.6

Frequency distributions created by using the
FREQUENCY
function

Using formulas to create a frequency distribution

Figure 17.7 shows a worksheet that contains test scores for 50 students in column B (the

range is named Grades). Formulas in columns G and H calculate a frequency distribution for

letter grades. The minimum and maximum values for each letter grade appear in columns

D and E. For example, a test score between 80 and 89 (inclusive) earns a B. In addition, a

chart displays the distribution of the test scores.