# Totalling Percentages Problem in Excel

## Question

I have a column of figures, each of which I want to display as the percentage of the total. Sometimes the total of the percentages does not come to 100% depending on the figures. Is there a way round this?

This is an interesting problem that we meet in various guises. We keep trying to find the ideal way of tackling it; but, even with the method we describe here, we do not consider we have fully achieved that aim yet.

The formula in cell B1 (copied to rows two to four) is this:

=A1/\$A\$5.

Percentage formatting is applied to column B.

The total—AutoSummed in B5—is incorrect.

To illustrate what we are up against here, this is the same spreadsheet with the decimal places exposed:

You can see that, even at these limits, we are faced with a problem!

In our solution, we need two columns, C and D here (usually column C would be hidden):

The formula in cell C1 (copied to the other cells as far as row 4) is this:

=ROUND(A1/\$A\$5,2).

Note that you round to two decimal places when you want to display resultant percentages with no decimal places. If you want to display to one decimal place, use this formula

=ROUND(A1/\$A\$5,3).

The formula in cell D1 is this (formulae in rows two to four in this column are simply of the form =C2):

=1-(SUM(\$C\$1:\$C\$4)-C1)

What this formula achieves is a fudge factor in the result in cell D1.

Applying a fudge factor is the only way we can see to make your percentages total 100%.