Pie Chart Percentage incorrect

[Guest]

I have a Pie Chart that I have created that I am having a problem with. The
chart is rounding numbers incorrectly.

One value (out of 16) is 19.13% of the total, yet the pie chart rounds this
down to 18%. Another value in the same chart is 18.75% of the total but it
is rounded up to 19%.

I was questioned about this discrepency and found that if I Format Data
Labels with 1 deicmal place, the values do show correctly (19.1% and 18.8%).
But of course now I'm being questioned as to why I changed the format.

Is there a way to fix my Pie chart so it shows the correctly rounded numbers
without a decimal place?

Is this 'hidden feature' fixed in Excel 2007?

 

[Bernard Liengme]

It would help if you told us the actual vlaues you are plotting then we can
test it.
best wishes
Bernard

 

[Guest]

The values will change based on the data. Here's what I am currently working
with:

Account Hold 34 4.25%
Awards 153 19.13%
Certification 49 6.13%
Directions 5 0.63%
Disbursements 46 5.75%
Documents 41 5.13%
Entrance/Exit 7 0.88%
FAFSA/SAR 77 9.63%
Loan Certification 9 1.13%
Payment Plan 5 0.63%
Registration 10 1.25%
Request Another Dept 71 8.88%
Student Accounts/Bursar 81 10.13%
Transcript Request 39 4.88%
Turn Around Time 23 2.88%
Type of Aid 150 18.75%

The 19.13% Awards are getting rounded down to 18% in the Pie chart.

Awards
18%

If I right click on the Award value in the pie chart and Format Data Labels,
it shows the 19% and not 18% as the sample in the Format dialog box.

Thanks for looking into it, Bernard.

 

[Guest]

I'm speculating but it appears that the pie-chart percentage calculation is
forced to equal exactly 100%. When you add up the rounded numbers given the
data below, it actually equals 101%. Excel must then drop the extra 1% off
of the largest number.

To get around this, one option would be to deselect the percentage label
option so that only the pies appear. Then calculate the correct percentages
using a helper column and the "round" worksheet function. Then use a tool
like John Walkenback's chart tool to apply your own data labels to the
existing pies. You can download the tool here . . .

http://www.j-walk.com/ss/excel/files/charttools.htm

--
John Mansfield
http://cellmatrix.net

 

[Guest]

I forgot to add, this Microsoft Knowledge Base article addresses the problem
and acknowledges that it is by design.

http://support.microsoft.com/kb/214033

Unfortunately it requires you to use decimal places. However, using the
chart tool will get you around this limitation.

--
John Mansfield
http://cellmatrix.net

 

[Guest]

Thanks John - you answered the most frustrating part of the problem - why?

" drop the extra 1% off of the largest number."

I understood about making the total come up to 100% but couldn't figure out
why that number. What's funny is that using my data the 18.75% is rounded up
to 19% but the 19.13% is then rounded down to 18% because the 19.13% is
higher than the 18.75%.

I create the Pie Chart on the fly with VB, so I can add the extra code to
figure the percentage and add that when I populate the Call Type cell to
create my own label with percentages. Thanks for the suggestion - I hadn't
thought of that.

Any idea whether 2007 addresses this issue? The KB doesn't list 2007.

Bryan..

 

[Andy Pope]

xl2007 displays 19% for for both values when no decimals are shown,
which means the percentages add up to 101%

Another good reason to consider another chart type

Cheers
Andy

 

[Jon Peltier]

I suspect the problem came as a result of some dim manager somewhere seeing
a pie chart with totals that added to 99% (draw a pie with three equal
wedges, and the labels will say 33%, 33%, and 34%), and complaining to
Microsoft that it didn't add to 100%. The fix then was implemented poorly
(it should not have been implemented at all), and we're stuck with the
results. Excel 2007 is also afflicted, by the way.

- Jon

 

[Jon Peltier]

xl2007 displays 19% for for both values when no decimals are shown, which
means the percentages add up to 101%

Aha, but Excel 2007 shows 33, 33, and 34% if the pie has three equal wedges.

Another good reason to consider another chart type

I meant to say that, but I'm glad someone has.

- Jon