Why can't my numbers add up to 100%?

[useless clueless]

I am doing absolutely masses of simple sums in Excel (X for Mac) to then produce charts for a report - but I am having to illustrate the actual
figures as whole percentages and that is getting me hot under the collar - as my little lists rarely add up to an exact 100%. Now, I can
understand where the discrepancy is creeping in with the rounding and all - but is there a sneaky way to get Excel to make the adjustment
for me so that the percentages always total 100%?

Here is an example from my sheet, where the actual figures are added to equal 200, and the cell showing the percentage figure has a simple
formula at present of for example "=(B71/B76)" where B71 is the actual figure (in this case, 12) and B76 is the sum of all the actual figures
(200 below) and the cell is then set to be a percentage with zero decimal places.

<link id="Main-File" rel="Main-File" href="file://localhost/Users/Fiona/Library/Caches/TemporaryItems/msoclip/0/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl28 {mso-number-format:"0\.0%";} .xl29 {mso-number-format:0%;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="150"> <!--StartFragment--> <col width="75"> <col width="75"> <tbody><tr height="13"> <td x:num="12.0" align="right" height="13" width="75">12</td> <td class="xl29" x:num="0.06" align="right" width="75">6%</td> </tr> <tr height="13"> <td x:num="105.0" align="right" height="13">105</td> <td class="xl29" x:num="0.525" align="right">53%</td> </tr> <tr height="13"> <td x:num="44.0" align="right" height="13">44</td> <td class="xl29" x:num="0.22" align="right">22%</td> </tr> <tr height="13"> <td x:num="31.0" align="right" height="13">31</td> <td class="xl29" x:num="0.155" align="right">16%</td> </tr> <tr height="13"> <td x:num="8.0" align="right" height="13">8</td> <td class="xl29" x:num="0.04" align="right">4%</td> </tr> <tr height="13"> <td x:num="200.0" align="right" height="13">200</td> <td class="xl28">
</td> </tr> <!--EndFragment--> </tbody></table>
So you can see that my percentages here total 101%!

I have spent hours trying to research a sneaky way and am starting to think I'm asking for the impossible - and will just have to 'adjust' the
percentages manually to make my 90 charts look accurate!

Many thanks as always, here's hoping.

 

[mikerickson]

There is a further problem with this.
If your data is
100
100
100
100
100
101

All round to 17% and the sum is 102%.
If only one of the values is fudged, that would result in
100 = 17% in most cases and either 101 or 100 = 15% in that one other case.

 

[Jobowo]

Yes, as mikerickson points out, the kluge can stand out if your data is of the "wrong" sort. However, if it is such (as in your example is) that either the data is more varied, extensive hard to spot; or because your audience is half-asleep, you can get away with a simple solution. Apparently yours isn't so a formula may be a bit too crude (and hard to implement). It's macro time again:

Not the best I've ever written but it will do the job. Install the macro (ALT-F11, Insert, Module and cut and paste), attach to keystroke eg CTL-SHIFT-K. Now select any column of data that needs an adjustment and press CTL-SHIFT-K. Bingo, your numbers should now add up.

I've chosen to adjust the largest number but you can change this. Just take the single quote off the beginning of the line with ** and take that
off the end of the line too.

There may be a few hiccups getting this working but it's one solution.

Option Base 1
Option Explicit
'
'   MACRO ENSURES THAT A COLUMN OF PERCENTAGES ADDS UP TO 100%
'   A variety of approaches are possible. This approach first
'   determines how much the column is out; if it is only by 1
'   the largest number is chosen to be 'fixed': 1% is added
'   (or subtracted depending on the need) to the largest
'   value in the group. If further "adjustments" need to be
'   made they are made to the number before if there is one
'   or continuing down. Finally, the total number is set to 1
'   (since the SUM(column) will now be out)


Public Sub Kluge()
    Dim List As Variant
    Dim i As Integer, j As Integer
    Dim n As Integer, maxn As Integer, mxi As Integer, Totn As Integer
    Dim delta As Single
    Totn = 0
    List = Selection
    On Error GoTo onerr         ' Usually only one cell selected
    For i = 1 To UBound(List)
        n = Int(100 * List(i, 1) + 0.5)
        If n > maxn Then        ' Mark position of max number
            maxn = n
            mxi = i
        End If
        Totn = Totn + n
    Next i
    '  mxi = int(ubound(list)/2)  ** 
    Totn = Totn - 100
    Select Case Totn
        Case 0: Exit Sub
        Case Is > 0: delta = -0.01
        Case Is < 0: delta = 0.01
    End Select
    ' Move mxi up 1 if count is out by more than 1%, so that
    ' difference can be distributed over 3 numbers
    If Abs(Totn) > 1 And mxi > 1 Then mxi = mxi - 1
    While Totn > 0
        Selection.Cells(mxi, 1) = List(mxi, 1) + delta
        Totn = Totn + 100 * delta
        mxi = mxi + 1
        Wend
    Selection.Offset(1, 0) = 1
    Exit Sub
onerr:
    List = MsgBox("Please select at least 3 cells", vbOKOnly)
End Sub

 

[Jobowo]

PS Note that the formula for those adjusted numbers will now be replaced by values, and the 100% at the bottom is no longer a sum, it is 1 (the numbers don't in fact add up to 100%!). I didn't want to replace all the values because then if you want to fiddle with it, you've lost all your formulae.

 

[Boller]

Why do the numbers have to "add up" ?

If each number (including the total) is rounded to whole numbers, then each number is accurate to the nearest whole number.

If adjustments are made so that one or more numbers are no longer rounded to the nearest whole number, then the adjusted number(s) are now inaccurate.

 

[Jobowo]

People attending presentations (or reading reports) sometimes don't understand rounding; they are suspicious of the fact that if you add up the presented figures, they don't add to 100%. Rather than spend yet another tedious 10 minutes explaining rounding to the audience, presenters and report writers would rather diddle the presented numbers to make them add up.

This is not quite the distortion you suggest. A rounded number is not an accurate number. It may be accurate in the sense that it conforms to mathematical rigor but it is not the true percentage. So it is a matter of re-adjusting the inaccuracies to avoid false and unnecessary suspicions rather than to indulge in subterfuge of any kind.

 

[Boller]

People attending presentations (or reading reports) sometimes don't understand rounding; they are suspicious of the fact that if you add up the presented figures, they don't add to 100%. Rather than spend yet another tedious 10 minutes explaining rounding to the audience, presenters and report writers would rather diddle the presented numbers to make them add up.

People attending presentations (or reading reports) might also be suspicious of the fact that one or more of the percentages has not been calculated properly.
And sometimes might not understand why the figures have been "diddled" to make them add up.
Adding a note about rounded figures is common practice in Companies' published reports.

This is not quite the distortion you suggest. A rounded number is not an accurate number. It may be accurate in the sense that it conforms to mathematical rigor but it is not the true percentage. So it is a matter of re-adjusting the inaccuracies to avoid false and unnecessary suspicions rather than to indulge in subterfuge of any kind.

But then, by your own submission, it means all of the rounded figures are not accurate.

"Diddling" the figures to make them add up does not amount to "re-adjusting the inaccuracies to avoid false and unnecessary suspicions ".
What is does amount to is increasing the inaccuracies.

Also, deliberately "diddling" in the hope that it won't be noticed sounds like subterfuge to me.

 

[drsarao]

deleted

 

[drsarao]

Formula approach to diddle the largest figure:

Excel Workbook
	A	B	C
1	12	6%	6%
2	105	53%	52%
3	44	22%	22%
4	31	16%	16%
5	8	4%	4%
6	200	100%	100%
7	Round sum	101%
Sheet13

Excel 2003

Cell Formulas
Range	Formula
B1	=A1/$A$6
B2	=A2/$A$6
B3	=A3/$A$6
B4	=A4/$A$6
B5	=A5/$A$6
B6	=SUM(B1:B5)
B7	=SUM(ROUND($B$1,2),ROUND($B$2,2),ROUND($B$3,2),ROUND($B$4,2),ROUND($B$5,2))
C1	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B1,B1+100%-$B$7,B1),B1)
C2	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B2,B2+100%-$B$7,B2),B2)
C3	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B3,B3+100%-$B$7,B3),B3)
C4	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B4,B4+100%-$B$7,B4),B4)
C5	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B5,B5+100%-$B$7,B5),B5)
C6	=IF($B$7<>100%,IF(MAX($B$1:$B$5)=B6,B6+100%-$B$6,B6),B6)

.

 

[tusharm]

Most charts that deal with this kind of issue include a caveat "Numbers may not add to 100% because of rounding errors." That is the easiest and most accurate way to deal with this issue.

However, given that your boss is a dimwit...

Unless your data will change frequently, the 2nd easiest way to deal with this kind of issue is "by inspection." Look at the results you have and decide which ones you want to fudge. I would pick the largest number because that way the impact on the number you adjust is the least.

Do be aware that a programmatic solution is extremely complex - and *any* "solution" will always be a no-win scenario.

Here's a simple way to create a no-win scenario.

Change your last number to 105. Now, the whole percentages total to 99%. If you add the 1% correction to the largest number, you will get 101% (since there are two largest numbers). If you add them to only one, it doesn't take a genius to note that the 2 105s have different percentage values attached to them. **

So, you could then try and fudge the actual data. Change one of the 105s to 104 and things will work out - as long as someone doesn't take too close a look at your numbers. After all, when the total is nearly 300, 1% corresponds to about 3. So, 104 and 105 cannot differ by 1%!

Also, if someone audits the model, your work will be suspect! Unless, of course, you include an explanation of how and why you fudged the data but any fudging of data makes one's work look less that top-notch.

So, no matter what one does, it remains a no-win scenario.

** While a trivial change to your data set resulted in an no-win scenario, other no-win scenarios are equally easy to create. Try 3 equal numbers or 6 equal numbers. The first rounds to 99% the second to 102%. If you try and correct either of them, the actual data are all equal and yet the percentages will be different. Again, doesn't take a genius to realize something's amiss.

I don't mind a kludge! But what would really suit is a formula that would add the two percentage cells above it and the two below it, deduct
them from 1 and make that middle cell the difference, whether it be less or more than it is?

Clear as mud, I know, but in my first example, it would be making the middle figure (22%) go down to 21% to make all five then total
100%.

Is that possible please, you Excel gurus?

Thanks!

 

[useless clueless]

I quite agree with all of you - it is madness that we have produced this data and then fiddle the figures just to make it look better on the screen, which is what is happening in my eyes. But... I'm not the boss, he is! So I just have to do what I'm told. It took me the best part of an hour just to explain again and again to him why the percentages didn't 'add up' and he refuses to see actual figures appear on the charts or have a caveat placed with it, so I'm left with little option. It makes me cringe to do it, but I'm fed up of fighting the cause with him and just need to get the job done!

Drsarao, thank you, thank you, thank you! That quick kludge has worked a treat and I will able to quickly copy it across all 240 blocks of data. Yes, this makes it inaccurate, but saves me making it inaccurate manually instead!

So a huge thanks to you all, my gurus, you've confirmed what I was already thinking and have helped me to do the worst anyway. You don't know how much it has eased my annoyance just to have you fight my corner with me, I'm truly grateful for this small thing.

Now, back to those charts!