Excel 2010 Bible

Part II: Working with Formulas and Functions

296

Using formulas to create a frequency distribution ..........................................

Figure 13.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 exam-

ple, a test score between 80 and 89 (inclusive) earns a B. In addition, a chart displays the distribu-

tion of the 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 by

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

Note
The preceding formula uses the COUNTIFS function, which first appeared in Excel 2007. For compatibility
with previous Excel versions, use this array formula:

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

FIGURE 13.7

Creating a frequency distribution of test scores.