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.

Jon,

Your correct, on the surface the objective of creating a formula that miscalculates percentages so that the worksheet agrees with a pie chart does seem to be a waste of time.  But, at least in my experience, here’s why it might be worth a try . . .

In a meeting where paper documents are handed out . . . for the most part I’ve found that all of the suits can add the percentages in their heads and tell if they don’t equal 100%.  However, they can’t do the division to calculate the percentages.  It follows that if they can’t do the division they never know if the calculation is off.  All they care about is seeing that the chart and table add to 100% and that the slices in the pie match each line in the table.

That being said, I’ve also found that a few managers who have been promoted to their highest level of incompetence will concentrate on trivial rounding issues because they don’t understand the underlying data or calculations.  It gives them something to point out in a meeting when they can’t contribute anything of substance.

Finally, as a rule I just avoid pie charts for all of the reasons you’ve noted and about 100 more.  After more thought, maybe it is a waste of time.

I wrote my post on pie chart rounding. While sorting the data in your second example, I discovered that Excel turned the three largest percentages from 23, 21, and 21% to 22, 20, and 21%. The interesting thing is that the wedge Excel labels as 20% has a larger value than the one it keeps at 21%.