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

useless clueless

New Member
Joined
Aug 14, 2008
Messages
18
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-ignore:padding; 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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
when you format a cell to 0 decimals, the displayed value is ROUNDED. But the ACTUAL value still remains.

If you show the decimals, you'll see that some of your percentages are actually smaller than they are DISPLAYED as...

This is very important rule in excel to learn.
Formatting a cell DOES NOT CHANGE the actual value of the cell.
It only changes the APPEARANCE of the value in the cell.
 
Upvote 0
The problem is rounding, if you increase your decimals to one place you will see that the %'s alongside 105 and 31 are actually 52.5% and 15.5% respectively. If you add the %'s up you will only get 100% as what you are seeing on screen (i.e. 53% and 16%) have been rounded up (but only in display not in actual value).

I would say you have two choices:
1. Display your %'s to at least one decimal place.
2. Continue to display with no dcimals but add a note to say that any 'apparent' discrepancies are due to rounding.
 
Upvote 0
Pls format the cell to 2 decimal place, u will get the below

<table style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" align="right" height="20">12</td> <td class="xl63" style="width: 48pt;" width="64" align="right">6.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">105</td> <td class="xl63" align="right">52.50%</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">44</td> <td class="xl63" align="right">22.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">31</td> <td class="xl63" align="right">15.50%</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">4.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">200</td> <td>
</td> </tr> </tbody></table>

Regards,
Paul
 
Upvote 0
Sorry, I think all 3 of us responders missed the part where you said you understood WHY it happens.......

As far as how to get around it, I don't see any way.
Other than as StuLux said, make a note to yourself and any user of your sheet that those discrepancies are expected.
You might have one column to show the decimals and one that doesn't...
So if there are discrepencies, you can show why..
 
Last edited:
Upvote 0
I would say you have two choices:
1. Display your %'s to at least one decimal place.
2. Continue to display with no dcimals but add a note to say that any 'apparent' discrepancies are due to rounding.

I would suggest alternative 2.

You may still have the same situation re rounding with alternative 1.

Instead of using SUM in the % column, use =B76/B76

If you start messing around with the rounding so that the % column adds up, you will have a cell that shows an incorrect percentage.
 
Upvote 0
Tools>Options>Calculation Tab. Is "Percision as Displayed" is selected? If so, uncheck it.

lenze
 
Upvote 0
Thank you to you all, so it is just as I feared.

Oh well, boss refuses to let me use decimal places as the charts are to be as 'clean' to look at as possible and won't accept me putting an error exclusion on them, so the data analysis will just have to be statistically incorrect instead!
 
Upvote 0
There's the old kluge of making the last item 100%-SUM(Previous Items). This is often done on Accounts Payable or Invoices, to make the discounts add up. Of course, you do need to have the extra check...

=IF(ABS(1-SUM(Above))<=0.01,1-SUM(Above),"Too big")
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top