Okay so I have been having a problem and would really appreciate any help. I have posted a table below with the aim of making this as simple/precise as possible.
Value % Rounded % Direction
10 1.515151515151520% 2% Up
20 3.030303030303030% 3% Down
30 4.545454545454550% 5% Up
40 6.060606060606060% 6% Down
50 7.575757575757580% 8% Up
60 9.090909090909090% 9% Down
70 10.606060606060600% 11% Up
80 12.121212121212100% 12% Down
90 13.636363636363600% 14% Up
100 15.151515151515200% 15% Down
110 16.666666666666700% 17% Up
T660 102%
Table explanation: Value is a number (total=660). The % is the percentage of the total ((value/660)*100%). Rounded is the % rounded at the percentage level rather than rounding the total. Direction is whether the rounding is up or down. (row begining with T, = Totals)
Problem: I understand why I am getting 102%. I am creating a report that uses percentages and I want the rounded percentages to add up to 100% but be as mathematically accurate as possible. Is it possible to determine which decimals in the % column are most significant to the overall increase in the total percentage? Is there some universal equation that can be derived to perform this?
I know that this is pretty vague, so please comment if you need any clarification, but any help would be appreciated.
Thank you!
Obviously you cannot do this and get the rounding correctly done. However, one possible method is the following:
Calculate the roundoff error, so for $1.515 \dots$ to $2$ the roundoff error is: $$|2 - 1.515...| = 0.4949\dots$$For the second entry, it would be: $$|3-3.0303\dots|=0.0303\dots$$ After you've done this for each entry, do the following:
If the total rounded percentage is greater than 100%: Find the entry with the largest error that rounds up, and change it so that it rounds down. A large error means it is close to the limit of being rounded down.
If the total rounded percentage is less than 100%: Find the entry with the largest error that rounds down, and change it so that it rounds up.
Repeat the above process until the rounded percentage is exactly $100 \%$.
(You could also do this algorithm with relative error, instead of absolute error.)