How to Total Percentages in Microsoft® Excel
How to add, or SUM, a column of percentages using an Excel formula so that the total comes to 100%.
Last updated on 2020-05-26 by David Wallis.
“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?”
Illustrating the Problem
This is a 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:
The formulas in Cells B1 and C1 (copied to rows two to four) is this:
Percentage formatting is applied to Columns B and C. Formatting applied to Column B restricts the display of percentages to whole numbers; to Column C, to 15 decimal places.
Created with AutoSum, the total neither in Cell B5, nor in Cell C5, is correct. The rounding effects of formatting on Column B, introduce significant errors in presentation such as that in Column B.
In Column C we come up against the amount of precision with which Excel can store numbers: 15 digits.
A Possible Solution
In my solution, we need two columns, B and C here (usually Column C would be hidden):
The formula in cell B1 (copied to the other cells as far as row 4) is this:
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:
The formula in cell C1 is this:
Formulae in rows two to four in this column are all the same:
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%. I’ve left to my subconscious the challenge of finding a superior solution
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.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
If you’d like an invoice to account for your donation, let me know how much you’re donating —
Thanks, in anticipation.
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.