View Single Post
  #5   Report Post  
Tushar Mehta
 
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