dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Totalling Percentages Problem in Excel

Last updated on 2018-08-01 by David Wallis.


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?“


Response

This is an challenging problem that I meet in various guises. I keep trying to find the ideal way of tackling it; but, even with the method I describe here, I don’t consider I 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:

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

Formulae in rows two to four in this column are all the same:

=C2

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

Applying a fudge factor is the only way I can find 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. I 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.

“You give 100 percent in the first half of the game, and if that isn't enough in the second half you give what’s left.”

Yogi Berra (1925 to 2015), baseball player and manager.