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 2-5, the calculation is relatively simple. You divide each component part by the total. This example has a cell that contains Total revenue (cell C9). You then divide each region’s revenue by the total to get a percent distribution for each region.
 |
| Revenue across regions |
How it works
This formula doesn’t have a lot to it. You’re simply using cell references to divide each component 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 Total does not adjust as you copy the formula down.
Alternative: Percent distribution without a dedicated
Total cell
You don’t have to dedicate a separate cell to an actual Total value. You can simply
calculate Total on the fly within the percent distribution formula. Figure 2-6
demonstrates how you can use the SUM function in place of a cell dedicated to
holding a Total. The SUM function adds together any numbers you pass to it.
Again, note the use of absolute references in the SUM function. Using absolute
references ensures that the SUM range stays locked as you copy the formula down:
=C3/SUM($C$3:$C$6)
 |
| % distribution |
you may also read Ready To Use Excel Formulas : Calculating a Percent Distribution
No comments:
Post a Comment