A Study of Pie Chart Rounding
Pie charts, as well as many other charts, can be built to show percentages of a whole. Unfortunately, many times the pie chart percentages do not tie in total to the total of the source document percentages. Microsoft acknowledges the problem via this knowledge base article titled Pie Chart Shows Incorrect Percentage Value in Excel.
As a potential work-around, I attempted to create a source document formula that works similar to the logic described in the knowledge base article. The purpose of the formula is to force the rounded percentages in the source report to always tie to the pie chart percentages.
Refer to the screenshot below:
A source range, named "Data", covers the range C4:C10. Column D reflects the rounded source document percentages. When added together, the percentages equal 101%.
Column E contains the alternative percentages that tie to the pie chart. The formula, entered as a multicell-array into the range E4:E10, is as follows:
{=IF((Data-ROW()/10^10)=LARGE((Data-ROW()/10^10),1),
1-SUMPRODUCT(((Data-ROW()/10^10)<>LARGE((Data-ROW()/10^10),1))
*(ROUND(Data/$C$11,2))),ROUND(Data/$C$11,2))}
The formula works essentially as the knowledge base article describes. It:
- Uses the ROW function to first break any ties that may occur when the same number appears two or more times.
- Using the numbers calculated by the ROW function, it then uses the LARGE function to determain which percentage is the maximum.
- If the percentage is the maximum, it subtracts the total of all of the the percentages excluding the maximum from 100%. All other percentages are calculated and rounded as normal.
In the example, the formula recognizes the rounded value for Gamma as the maximum. Because the data does not add to 100%, the formula subtracts one from the Gamma value to force the percentages to round to 100%. Likewise, the percentages in the pie chart show the same values.
To test the formula, I used the RANDBETWEEN function and a macro to quickly test 10,000 random scenarios. The formula appeared to work well with values that round in total to 99% or 101%. However, when values round to 98% or 102%, I did find occurances where the formula returns values that differ from the pie chart. In those cases Excel does not seem to assign the difference to one single value. Rather, Excel appears to pick two of the same values and distribute the difference evenly. Given the logic as described in the knowledge base article, I'm at a loss to explain why.
Below is an example where the pie chart percentages differ from the formula:
The formula calculates Beta to be the maximum and subtracts two from it to force the total to equal 100%. However, Excel subtracts one from Beta and Delta to get to 100%.
So your objective is a formula that miscalculates percentages so that the worksheet agrees with your pie chart? LOL
You could add custom data labels to the chart (Rob Bovey’s Chart Labeler, http://appspro.com), and use a column properly calculated percentages as the data label source. Or you could add a digit of precision to the chart’s data labels.
As if we needed more reason to avoid pie charts, we find Microsoft can’t even do pie chart percentages right. I suspect this error arose “by design”. Some big shot executive from a major Office licensee complained that his percentages didn’t add to 100%, so they added random 1% or 2% amounts to the pie labels until the magic 100% was reached. The suit was used to dealing with $$billions, so rounding in the decimal digits was unknown to him.