Numbers chart on budget spreadsheet

If you are interested in the largest remainder method, which I had totally forgotten about, here is a rough implementation just for fun.

Column A is manually entered categories

Column B is the actual data for each category

D1 =100−SUM(D)

G1 = SUM(G) (just a check, not necessary)

C2=100×B2÷SUM(B)

D2=INT(C2)

E2=ROUND(C2−D2,4)+ROW()÷10000000

F2 =RANK(E2,E)≤D$1,1,0)

G2 =D2+F2

Chart uses column G as its data.

Column E is the remainder between the actual value and the rounded value except no two values can be exactly the same so I added a tie breaker. I rounded the result and tacked the row number onto the right of it. You can see the result in the last two rows. Two equal percentages in column C but one rounded up and the other rounded down. The method I used favors values in lower rows (in higher row numbers) but it guarantees uniqueness.

This is a common problem with rounding, not a problem with Numbers or your chart. As an example, take the following two numbers: 1.5 and 2.5. They sum to 4. But if you format them to display with no decimal places they look like 2 and 3 which visually sums to 5. The percentages on your chart are doing the same thing most likely. If you increase to one decimal place, you'll get closer to the correct sum but it may still be off by tenths of a percent or more depending on how many of your numbers rounded up and how many rounded down.

I'm not really sure the best way to get it to add up to exactly 100%. You would have to manipulate the data in your table to have it add up that way. Not so easy, though. Given the example with numbers 1.5 and 2.5 rounded to no decimals, you would have to decide which one rounds down and which one rounds up so they still sum to 4. Do they round to 1 and 3 or to 2 and 2? Neither is correct and neither gives a proper display of the data on a chart. I think it is better to increase the number of decimals shown or live with it not being exactly 100%.

It's very hard to say without knowing more specifics. What budget spreadsheet? Can you post a screenshot?

Just a guess, but if you are getting over 100% then perhaps there is unintended rounding of amounts, i.e. the value displayed in a cell is 100.00 whereas the actual value as a result of multiplication or division is, say, 99.60. You may then need to consider using the ROUND or TRUNC functions.

SG

Two common ways of handling this if you prefer to keep the percentages rounded so as not to suggest false precision:

1) Use the "largest remainder" method sometimes applied to election results.

2) Just add a footnote: "Percentages may not total 100 due to rounding."

SG