A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Bug in Rounding percentages of Bar of Pie chart



 
 
Thread Tools Display Modes
  #1  
Old August 22nd 05, 05:07 AM
Murat Gulbay
external usenet poster
 
Posts: n/a
Default 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
percentages fail for some particular data like this.. Any comments about this?
Ads
  #3  
Old August 22nd 05, 11:16 PM
Murat Gulbay
external usenet poster
 
Posts: n/a
Default

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
> > percentages fail for some particular data like this.. Any comments about
> > this?

>
>
>

  #4  
Old August 23rd 05, 02:35 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default

I read a discussion about this, how Excel violates rounding of three 33% segments so
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
>>>percentages fail for some particular data like this.. Any comments about
>>>this?

>>
>>
>>


  #5  
Old August 23rd 05, 05:12 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default

In article >, =?Utf-
8?B?TXVyYXQgR3VsYmF5?= <Murat > says...
> (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
> percentages fail for some particular data like this.. Any comments about this?
>

Yes, there definitely seems to be something screwy going on in the way
XL computes whole numbers as percentages for a bar-in-pie (and I
presume pie-in-pie) chart.

FWIW, when the displayed format is a whole number, XL always attempts
to round the total % to 100. So, on occassion it adds 1 (or subtract)
1 to make things work. It does so by affecting the largest number
displayed. This is not something out of the ordinary. It is done
routinely when adding and rounding numbers. To see the effect of XL
automatically subtracting one, plot 1.12, 1.61, and 1.8. As
percentages these work out to be 24.7%, 35.5%, and 39.7%, or with zero
decimal places 25%, 36%, and 40%. Note that the total comes to 101%.
In a pie chart XL will show the 40% as 39%.

Of course, that doesn't explain what it does with bar-in-pie. It is
obviously trying to round the result and for some reason adding numbers
to both the originally largest value and the new largest value (the
chunk that corresponds to the sum of the bars).



--
Regards,

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




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Activating a Chart object Hari Prasadh Charts and Charting in Excel 6 August 2nd 05 07:22 PM
Urgent Chart Assistance Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM
Urgent Chart Questions Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Urgent Chart Assistance Requested Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Scrollbar on Chart Jumps to Left when Chart is Clicked Bob Charts and Charting in Excel 5 May 1st 05 02:06 AM


All times are GMT +1. The time now is 08:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.