Force Pie Chart to be 100% in Numbers

Even with a decimal place it might not add up to 100%. That's just how rounding works. Excel apparently has an algorithm to tweak the numbers slightly to make it equal 100% but it can give perverse results. Numbers that are the same in the table may be different percentages on the chart.

If you need it to be 100% exactly on the chart, regardless of anything else, you will have to so something in your table to make it happen. Below is one method I found online that I implemented in Numbers. I don't know if it is the best method or gives the best results but it ends up at 100%. The result is kinda iffy on the numbers you provided but Excel's results are not that great either.

Columns A and B are your data

C2 =B2÷B$13+SUM(OFFSET(C2,−1,0))

D2 =ROUND(C2,2)

E2 =SUM(OFFSET(D2,−1,0))

F2 =D2−SUM(OFFSET(D2,−1,0))

Fill down with all those to complete the columns

B13 (in footer row of the table) =SUM(B)

F13 = SUM(F) but it isn't used for anything

Chart the rightmost column. Hide the other columns if you like.

I used SUM(OFFSET(cell_reference,-1,0)) in the formulas so that the table can be sorted. Basically it is referring to a cell in the previous row. OFFSET makes the formula sortable. SUM makes it not fail in row 2 from trying to use text from the header as a number. SUM ignores text so it turns that text into a 0.

Ian Aberle1 wrote:

better than people saying your pie chart doesn't add up to 100.

This artifact of rounding is common. Why not just add a small footnote to the effect that "Percentages may not add exactly to 100"? That avoids unneeded complexity that adds nothing to the idea you are trying to convey. People understand immediately.

SG

Here are your numbers. The last column is the actual percentage.

You can search the web for additional algorithms that might give better results. The one I used was simple to implement. Whatever Excel uses has its own flaws. When wedges get below a few percent, I think all algorithms will be iffy. Forgetting about algorithms entirely, just rounding 0.54% to a whole percentage is a relatively huge change for that wedge, whichever way you go.

SGIII wrote:

Why not just add a small footnote to the effect that "Percentages may not add exactly to 100"?

That is a quality idea.