Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

Figure 7-8:
Creating a frequency distribution of test scores.

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)

Using the Analysis ToolPak to create a frequency distribution

After you install the Analysis ToolPak add-in, you can use the Histogram option to create a

frequency distribution. Start by entering your bin values in a range. Then choose Data

➜

Data Analysis to display the Data Analysis dialog box. Next, select Histogram and click OK. You

should see the Histogram dialog box shown in Figure 7-9.

➜

Analysis