Excel 2010 Bible

Chapter 13: Creating Formulas That Count and Sum

297

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.

1. Enter your bin values in a range.


2. Choose Data ➪ Analysis ➪ Analysis to display the Data Analysis dialog box. If this
   command is not available, see the sidebar, “Is the Analysis ToolPak Installed?”.


3. In the Data Analysis dialog box, select Histogram and then click OK. You should see
   the Histogram dialog box shown in Figure 13.8.


4. Specify the ranges for your data (Input Range), bins (Bin Range), and results
   (Output Range), and then select any options. Click OK. Figure 13.9 shows a fre-
   quency distribution (and chart) created with the Histogram option.

Caution
Note that the frequency distribution consists of values, not formulas. Therefore, if you make any changes to
your input data, you need to rerun the Histogram procedure to update the results. n

FIGURE 13.8

The Analysis ToolPak’s Histogram dialog box.