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-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.
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.