Chapter 13: Using Formulas for Conditional Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . .
10
Calculating a percent distribution
Percent distribution is a measure of how a metric (such as total revenue) is distributed
among the component parts that make up the total. As you can see in Figure 10.5, the cal-
culation is relatively simple. You divide each component part by the total. In this example,
we have a cell that contains the total revenue (cell C9). We then divide each region’s rev-
enue by the total to get a percent distribution for each region.
FIGURE 10.5
Calculating a percent distribution of revenue across regions
There isn’t much to this formula. You’re simply using cell references to divide each com-
ponent value by the total. The one thing to note is that the cell reference to the total is
entered as an absolute reference ($C$9). Using the dollar symbols locks the reference in
place, ensuring that the cell reference pointing to the total value does not adjust as you
copy the formula down.
You don’t have to dedicate a separate cell to an actual total value. You can simply calculate
the total on the fly within the percent distribution formula. Figure 10.6 demonstrates how
you can use the SUM function in place of a cell dedicated to holding a total value. The SUM
function adds together any numbers you pass to it.
FIGURE 10.6
Calculating percent distribution with the SUM function