Aaarrgh! The result should be 100% but it isn't

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?


Answer

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.

This spreadsheet illustrates your question:

Percentages Spreadsheet

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:

Precision Spreadsheet

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):

DMW Spreadsheet

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%.

A down side of this technique is that anyone reading your spreadsheet and checking your figures with a calculator will not be able to duplicate your results.

Furthermore, if the value in cell A1 is relatively small, the fudge factor will introduce a substantial percentage error in the value in cell D4. We have not found a formula that will apply the fudge factor to the largest value in column A automatically.


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.