Formula Excel : 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 Image 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.

Image 2-5: Calculating a percent distribution of 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.

Note : For more information see on absolute and relative cell references.

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. Image 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)

Image 2-6: Calculating percent distribution with the SUM function.

Categories: Formula