Microsoft Office Tutorials and References

In Depth Information

**Creating a frequency distribution**

FIGURE 17.7

Creating a frequency distribution of test scores

The formula in cell G2 that follows counts the number of scores that qualify for an A:

=COUNTIFS(Grades,">="&D2,Grades,"<="&E2)

You may recognize this formula from a previous section in this chapter (see “Counting cells

based on multiple criteria”). This formula was copied to the four cells below G2.

The preceding formula uses the
COUNTIFS
function, which i rst appeared in Excel 2007. For compatibility with

previous Excel versions, use this array formula:

{=SUM((Grades>=D2)*(Grades<=E2))}

The formulas in column H calculate the percentage of scores for each letter grade. The

formula in H2, which was copied to the four cells below H2, is:

=G2/SUM($G$2:$G$6)

Using the Analysis ToolPak to create a frequency distribution

The Analysis ToolPak add-in, distributed with Excel, provides another way to calculate a

frequency distribution: