Chart percentage is wrong

[dliquin]

A data value is exactly 29% (29.00000000000 etc.) of the total, but when I create a chart, rounding (I guess) causes that value to be shown as 28%. I understand the rounding issue, but because the data is whole numbers that add up to $1,000,000.00, this stands out and makes the chart look wrong. Is there some way to have the chart calculate percentages to another place or 2 so the rounding isn't such a problem? Or ???

 

[Jerry W. Lewis]

If the total is exactly 1,000,000 then a value would have to be less
than 285,000 to round to 28%. Other than that, it is difficult to say
much since you have provided so few details.

Jerry

A data value is exactly 29% (29.00000000000 etc.) of the total, but

when I create a chart, rounding (I guess) causes that value to be

shown as 28%. I understand the rounding issue, but because the

data is whole numbers that add up to $1,000,000.00, this stands out

and makes the chart look wrong. Is there some way to have the

 

[dliquin]

No, the data figure is exactly $290,000.00 and so should be 29.000000000000000 etc. %, but Excel changes it to 28% whenever I create a chart

What other information can I provide

Thanks
Dary

 

[Jerry W. Lewis]

Your data values and instructions on how you created the chart would be
a good start.

When I create a pie chart with one cell containing exactly 290,000 and
all cells totaling exactly 1,000,000 Excel shows the percentage as 29%.

Jerry

 

[Jon Peltier]

When I read this first, I suspected the total was incorrect, maybe
adding to 990,000.

- Jon

 

[dliquin]

Ok, here are the values:
290,000.00
219,000.00
128,353.01
105,720.00
99,500.00
30,526.99
20,400.00
16,500.00
It's a simple 2 column spreadsheet with the headings in the 1st column and the above values in the 2nd column. I'm not sure how the original chart was created (it's in a file my daughter created for a school project), but I started from scratch with the same values and got the same result. I inserted a chart and highlighted the data range, and it still comes up with 28% for the $290,000.00 figure.

 

[Jon Peltier]

The total of the column of figures is 910,000.00, so 290,000.00 is
actually 31.8681% of the total. I don't know how you got 28%. I wonder
if the 28% figure is something that was manually typed in place.

- Jon

 

[Andy Pope]

Assuming dliquin left off a value of 90,000.00 then the problem is
related to rounding. Although rounding the sum of the values NOT the 29%
value. But I don't understand how the algorithm determines that 29%
needs to be displayed as 28%

Here are the values I got when displayed a zero and one decimal place.

0% 0.0%
---- -----
28% 29.0%
22% 21.9%
13% 12.8%
11% 10.6%
10% 10.0%
3% 3.1%
2% 2.0%
2% 1.7%
9% 9.0%

If you sum the 0% set of values and the 28% was to be displayed as 29%
it would total 101%, which is not possible in a pie.

The only way to get 28% is to show the percentages to 1 decimal place.

The total of the column of figures is 910,000.00, so 290,000.00 is
actually 31.8681% of the total. I don't know how you got 28%. I wonder
if the 28% figure is something that was manually typed in place.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

--

Cheers
Andy

http://www.andypope.info

 

[dliquin]

I'm sorry - I haven't done a very good job of communicating

I did leave off the $90,000 figure. The total of the figures is exactly $1,000,000.00. None of the chart percentages have been entered manually. Excel came up with the 28%. It is wrong and I would like to find some way to fix it

Thanks
Dary

 

[Andy Pope]

Hi Daryl,

To get the pie to display 29% then set the data labels to de displayed
to 1 decimal place.

Otherwise create an extra column of figures and use these as custom data
labels. BUT the figures will add to 101%

This free addin will help link the data labels to cells.
Rob Bovey's XY Chart Labeler:
http://www.appspro.com/utilities/utilities.asp

 

[Tushar Mehta]

I don't know if this is good or bad, but XL is 'correcting' the
displayed percentages so that they add up to 100% Checking back, this
behavior is present in XL97 through 2002. But, your bringing it to
people's attention is the first I've noticed it.

And, the algorithm it is using seems to be to adjust the largest values
to get the correct results. You can test with the following:

Suppose your data are in A1:A9, with A9 containing the missing 90,000.

In B1, enter the formula =A1/SUM($A$1:$A$9)
In C1, enter the formula =ROUND(B1,2)
Copy B1:C1 to 2:9. Format B:C as % with 1 decimal place.
In C10, enter the formula =SUM(C1:C9). Note that it will sum to 101%
Create the pie chart and show the % values. The 29% will show up as
28% but the total in the chart is 100%!

Now, for the test.

First, reduce the 90,000 to 89,000 (that's because I needed to borrow a
thousand from somewhere).

Next, bump the 219,000 to 255,000. Also reduce the 290,000 to 255,000.
The total will still be 1 million. Now, the first two are each 25.5%
of the total and should be 26% rounded to zero decimals. C10 will show
102%. However, the chart will show 25% for each of the two items and,
with this correction, XL still maintains the total % shown in the pie
chart at 100%

Now, change the first 255,000 to 254,000 and the second to 256,000. XL
will change the first to 25% and the second in the worksheet will
become 26%. The total in C10 will be 101%. In the chart, the 2nd
entry will remain at 25% and the total will remain at 100%.

Good? Bad? Intentional? Accident? With MS one never really knows.
<g>

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Nice detective work, Sherlock!

- Jon

 

[Tushar Mehta]

Thank you, Watson. <g>

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Nice detective work, Sherlock!

- Jon

<snip>