Microsoft Office Tutorials and References

In Depth Information

**Using Histograms to Track Relationships and Frequency**

2.
Create a new column that holds the FREQUENCY formulas. Name the new column Frequency

Formulas, as seen in Figure 9-10.

Excel’s FREQUENCY function counts how often values occur within the ranges you specify in

a bin table.

3.
Select a number of cells equal to the cells in your bin table.

4.
Type the FREQUENCY formula you see in Figure 9-10 and then press Ctrl+Shift+Enter on your

keyboard.

The FREQUENCY function does have a quirk that often confuses first-time users. The

FREQUENCY function is an
array formula
— that is, it’s a formula that returns many

values at one time. In order for this formula to work properly, you have to press

Ctrl+Shift+Enter after typing the formula. If you just press the Enter key, you won’t get

the results you need.

Note

Figure 9-10:
Type the FREQUENCY formula you see here; be sure to hold down the Ctrl+Shift+Enter

keys on your keyboard.

At this point, you should have a table that shows the number of sales reps that fall into each of your

bins. You could chart this table, but the data labels would come out wonky. For the best results, build

a simple chart feeder table that creates appropriate labels for each bin, which you do as follows:

1.
Create a new table that feeds the charts a bit more cleanly (see Figure 9-11). Use a simple

formula that concatenates Bins into appropriate labels. Use another formula to bring in the

results of your FREQUENCY calculations.

In Figure 9-11, we made the formulas in the first record of the chart feeder table visible.

These formulas are essentially copied down to create a table appropriate for charting.

2.
Use your newly created chart feeder table to plot the data into a column chart.

Figure 9-12 illustrates the resulting chart. You can certainly use the initial column chart as

your histogram.

If you like your histograms to have spaces between the data points, you’re done. If you like

the continuous blocked look you get with no gaps between the data points, follow the next

few steps.