Remember Me?

#1
August 22nd 05, 05:07 AM
 Murat Gulbay Posts: n/a
Bug in Rounding percentages of Bar of Pie chart

(Excel 2003)
Suppose sales values for January to June are 25,50,90,45,100, and 75,
respectively.
Bar of pie chart with a second plot containing the sales values whose
percentage values are less than 15%. The problem is that, Excel draws the pie
chart with a total percentage of 102 %. The percentage of the total sales
included in the bar is 33%. Now if you increase its decimal, it is seen that
its actual value is 31.2 %. How Excel 2003 rounds 31.2 % as 33 %. Now
changing sales value for April from 45 to 47, it is seen that bar percentage
becomes 34 %, and total percentage for the pie becomes 103 %... When
increasing the decimal for 34 % it has changed to 31.5 percent. Chart

#3
August 22nd 05, 11:16 PM
 Murat Gulbay Posts: n/a

This is not an aproximation, just an inconsequent way of rounding error. If
you perform same calculations by excel formulas in cells you never get such a
bombastic approximation error. In your case, the value shown as 33% becomes
33.3 % when you increase the decimal. Here, what the problem is that 31.5 %
becomes 34 % upon decreasing decimal or vice versa, which denies the
theoritical rounding rules. When calculated in cells it is normal and
correct. But charting the same values gives such kind of errors.

"Bernard Liengme" wrote:

What sort of comment do you want?
If I divide a pie in three part, father gets 33%, mother gets 33% and baby
get 33%. But that is only 99%; did Goldilocks eat the other 1%. Oh no, you
say, every one gets 33.333333% but that still adds to 99.999999%. This time
G gets a crumb.
When ever you make approximations, you get approximations.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Murat Gulbay" <Murat wrote in message
...
(Excel 2003)
Suppose sales values for January to June are 25,50,90,45,100, and 75,
respectively.
Bar of pie chart with a second plot containing the sales values whose
percentage values are less than 15%. The problem is that, Excel draws the
pie
chart with a total percentage of 102 %. The percentage of the total sales
included in the bar is 33%. Now if you increase its decimal, it is seen
that
its actual value is 31.2 %. How Excel 2003 rounds 31.2 % as 33 %. Now
changing sales value for April from 45 to 47, it is seen that bar
percentage
becomes 34 %, and total percentage for the pie becomes 103 %... When
increasing the decimal for 34 % it has changed to 31.5 percent. Chart
this?

#4
August 23rd 05, 02:35 AM
 Jon Peltier Posts: n/a

the sum is 100%, not 99%. I remember thinking at the time, that it's worth adding a
digit of precision to avoid this behavior. I notice your numbers also work out fine
with an extra digit.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Murat Gulbay wrote:

This is not an aproximation, just an inconsequent way of rounding error. If
you perform same calculations by excel formulas in cells you never get such a
bombastic approximation error. In your case, the value shown as 33% becomes
33.3 % when you increase the decimal. Here, what the problem is that 31.5 %
becomes 34 % upon decreasing decimal or vice versa, which denies the
theoritical rounding rules. When calculated in cells it is normal and
correct. But charting the same values gives such kind of errors.

"Bernard Liengme" wrote:

What sort of comment do you want?
If I divide a pie in three part, father gets 33%, mother gets 33% and baby
get 33%. But that is only 99%; did Goldilocks eat the other 1%. Oh no, you
say, every one gets 33.333333% but that still adds to 99.999999%. This time
G gets a crumb.
When ever you make approximations, you get approximations.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Murat Gulbay" <Murat wrote in message
...

(Excel 2003)
Suppose sales values for January to June are 25,50,90,45,100, and 75,
respectively.
Bar of pie chart with a second plot containing the sales values whose
percentage values are less than 15%. The problem is that, Excel draws the
pie
chart with a total percentage of 102 %. The percentage of the total sales
included in the bar is 33%. Now if you increase its decimal, it is seen
that
its actual value is 31.2 %. How Excel 2003 rounds 31.2 % as 33 %. Now
changing sales value for April from 45 to 47, it is seen that bar
percentage
becomes 34 %, and total percentage for the pie becomes 103 %... When
increasing the decimal for 34 % it has changed to 31.5 percent. Chart
this?

#5
August 23rd 05, 05:12 PM
 Tushar Mehta Posts: n/a

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Hari Prasadh Charts and Charting in Excel 6 August 2nd 05 07:22 PM Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM Bob Charts and Charting in Excel 5 May 1st 05 02:06 AM

All times are GMT +1. The time now is 01:07 AM.