Microsoft Office Tutorials and References

In Depth Information

Figure 7-7:
Frequency distributions created using the FREQUENCY function.

Using formulas to create a frequency distribution

Figure 7-8 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) qualifies for a B.

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

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

You may recognize this formula from a previous section in this chapter. (See “Counting cells that meet multiple

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

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)