To make a contribution by Pay Pal in USD ($ US) -ĭMW 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. To make a contribution by Pay Pal in GBP (£ sterling).
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.
Returning this new challenge to my subconscious, I’m half anticipating it’s going to point me towards using VBA. Things looked promising until testing with the same value accorded to two cells in Column A: If it’s the greatest, then the 1-(SUM($B$1:$B$4)-B1) fudge factor is the result of the formula if it’s not, then the value in Cell B1 is the result. The IF uses A1=MAX($A$1:$A$4) to examine the value in Cell A1 to determine whether or not that value is the greatest in the range of Cells A1 to A4. Well, taking my own advice, I left the conundrum to my subconscious, which dutifully brought the following suggestion to my attention:Įxamining the formula in Cell C1, which is copied down Column C: In the above the error is an extreme 50% accruing in Row 1. The closing two paragraphs of the section above express my concern over the technique described - the strong possibility of substantial errors when the fudge factor is applied to a value that low in comparison to others: I’ve left the challenge of finding a superior solution to my I have not found a formula that will apply the fudge factor to the largest value in column A automatically.Īpplying a fudge factor is the only way I can find to make your percentages total 100%. What all this achieves is apply a fudge factor to the result in cell C1.Ī 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.įurthermore, if the value in cell A1 is relatively small, the fudge factor will introduce a substantial percentage error in the value in cell C1. If you want to display to one decimal place, use this formula:įormulae in rows two to four in this column are all the same:
Note that you round to two decimal places when you want to display resultant percentages with no decimal places. The formula in cell B1 (copied to the other cells as far as row 4) is this: In my solution, we need two columns, B and C here (usually Column C would be hidden): In Column C we come up against the amount of precision with which Excel can store numbers: 15 digits. The rounding effects of formatting on Column B, introduce significant errors in presentation such as that in Column B. Formatting applied to Column B restricts the display of percentages to whole numbers to Column C, to 15 decimal places.Ĭreated with AutoSum, the total neither in Cell B5, nor in Cell C5, is correct. Percentage formatting is applied to Columns B and C. The formulas in Cells B1 and C1 (copied to rows two to four) is this: This spreadsheet illustrates your question: 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 is a challenging problem that I meet in various guises. Sometimes the total of the percentages does not come to 100% depending on the figures. “I have a column of figures, each of which I want to display as the percentage of the total. If you read this article before this update, then please note the section, Targeting the Fudge, that addresses my main concern about the technique I posted originally. How to add, or SUM, a column of percentages using an Excel formula so that the total comes to 100%. (e) How to Total Percentages in Microsoft ® Excel