Microsoft Office Tutorials and References

In Depth Information

**Using Histograms to Track Relationships and Frequency**

Adding a cumulative percent

A nice feature to add to your histograms is a cumulative percent series. With a cumulative percent

series, you can show the percent distribution of the data points to the left of the point of interest.

Figure 9-14 shows an example of a cumulative percent series. At each data point in the histogram,

the cumulative percent series tells you the percent of the population that fills all the bins up to that

point. For instance, you can see that 25% of the sales reps denoted sold 15 units or fewer. In other

words, 75% of the sales reps sold more than 15 units.

Take another look at the chart in Figure 9-14 and find the point where you see 75% on the

cumulative series. At 75%, look at the label for that Bin range (you see 35–45). The 75% mark tells you that

75% of sales reps sold between 0 and 45 units. This means that only 25% of sales reps sold more than

45 units.

Figure 9-14:
The cumulative percent series shows the percent of the population that fills all the bins up to each

point in the histogram.

To create a cumulative percent series for your histogram, follow these steps:

1.
After you perform Steps 1 through 5 to create a histogram (outlined earlier in this chapter),

add a column to your chart feeder that calculates the percent of total sales reps for the first

bin (see Figure 9-15).

Note the dollar symbols (
$
) used in the formula to lock the references while you copy the

formula down.

Figure 9-15:
In a new column, create a formula that calculates the percent of total sales reps for the

first bin.