$\begingroup$

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!

$\endgroup$ 2

1 Answer

$\begingroup$

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.)

$\endgroup$

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy