Rounding trouble: Column must sum to 100%

[dgarland]

I have a manual process I would like to automate. Every time that I trade a portfolio I need to take balances after any changes, and round to the nearest 0.01% (two decimal places). The problem is, using your typical =ROUND(,4) formula for each row in the column, you end up with + or - 0.01% to 0.03% depending on how the rounding comes out.

Here is an example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Exact[/TD]
[TD]Rounded
[/TD]
[/TR]
[TR]
[TD]3.1066
[/TD]
[TD]3.11[/TD]
[/TR]
[TR]
[TD]19.8766[/TD]
[TD]19.88[/TD]
[/TR]
[TR]
[TD]13.7429[/TD]
[TD]13.74[/TD]
[/TR]
[TR]
[TD]19.8655[/TD]
[TD]19.87[/TD]
[/TR]
[TR]
[TD]10.7330[/TD]
[TD]10.73[/TD]
[/TR]
[TR]
[TD]18.9274[/TD]
[TD]18.93[/TD]
[/TR]
[TR]
[TD]13.748[/TD]
[TD]13.75[/TD]
[/TR]
[TR]
[TD]=100[/TD]
[TD]=100.01[/TD]
[/TR]
</tbody>[/TABLE]



I found a solution, but the logic is not perfect. The solution I found adds more columns to my example, making a cumulative exact column, a baseline rounded column, and a final rounded column. The logic is such that it compares the cumulative exact number to a rounded cumulative baseline to come up with a rounded "need" for each row. In practice, this means that the table goes line by line, stacking the difference between the exact and the rounded numbers until it those differences warrant a movement, and repeats this process down the sheet.

This particular method was found here: algorithm - How to make rounded percentages add up to 100% - Stack Overflow
Relevant post was by paxdiablo on 11/20/2012.

This doesn't meet my needs because:
A) it can result in changes that are nonsensical. In the example above, it changes 19.88 to 19.87, then 13.74 to 13.75, and 19.87 to 19.86. - Logically, you could just take 0.01 from the highest value to minimize impact: change 19.88 to 19.87.
B) it can result in changes when no changes need to be made: see A) above, it could be done after the first change, but it changes two additional times to get to 100.

I feel like I am spinning my wheels here, but I am really looking for something that can take a column of rounded values, minimize the changes, but change the values to reach another value. In this case, a column of percentages to reach 100%. I prefer not to use VBA if possible (multiple users may use this sheet, and making sure they use a macro correctly has caused problems in the past).

 

[oldbrewer]

if 3.11 becomes 3.12 this is an increase of (around) one third of one percent
if 19.87 becomes 19.88 this is an increase of (around) one twentieth of one percent

so find the total discrepancy and add it to (or subtract it from) the largest percentage in the list.....

 

[oldbrewer]

or you could spread it to the 2 biggest, or 3 biggest.............

 

[Jonmo1]

Maybe instead of rounding each individual value, then summing the results of the rounded values..

Sum the UNrounded values, then round the result of the sum.

 

[Obied70]

Your exact data rounded to .001% gives a sum of 100.00 so if you can change the way you round your exact data you'll be good. Otherwise I'm not sure.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Exact[/TD]
[TD="class: xl65, width: 64"]Rounded[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]3.1066[/TD]
[TD="class: xl65, width: 64, align: right"]3.107[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]19.8766[/TD]
[TD="class: xl65, width: 64, align: right"]19.877[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]13.7429[/TD]
[TD="class: xl65, width: 64, align: right"]13.743[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]19.8655[/TD]
[TD="class: xl65, width: 64, align: right"]19.866[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]10.733[/TD]
[TD="class: xl65, width: 64, align: right"]10.733[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]18.9274[/TD]
[TD="class: xl65, width: 64, align: right"]18.927[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]13.748[/TD]
[TD="class: xl65, width: 64, align: right"]13.748[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]100.00[/TD]
[TD="class: xl66, align: right"]100.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[dgarland]

Need to end up with 2 decimal places - not just for display purposes, there is an export that is needed at .00

 

[dgarland]

Maybe instead of rounding each individual value, then summing the results of the rounded values..

Sum the UNrounded values, then round the result of the sum.

This has the same problem - it incrementally makes the rounding changes as you go down the list, instead of considering a total column view.

 

[Jonmo1]

I don't follow

This will NOT round any values in A1:A10, you will get the REAL sum value.
=SUM(A1:A10)

This will round the result of the SUM, not each individual value in A1:A10
=ROUND(SUM(A1:A10),2)


You may still get rounding issues due to the Floating Point Precision issue in Binary systems.
https://support.microsoft.com/en-us/kb/78113

 

[dgarland]

I don't follow

This will NOT round any values in A1:A10, you will get the REAL sum value.
=SUM(A1:A10)

This will round the result of the SUM, not each individual value in A1:A10
=ROUND(SUM(A1:A10),2)


You may still get rounding issues due to the Floating Point Precision issue in Binary systems.
https://support.microsoft.com/en-us/kb/78113

Sorry I misunderstood. Yes I could round the final summed column, but my issue is each data point drives other portions of my worksheet. I know it is close to 100%, i need each value to sum up to 100% at 2 decimal places.

 

[Jonmo1]

You have 2 sets of numbers already, the original unrounded vlaues, and a column with a round function..

Do the sum on the original unrounded, but let your 'other portions' use the rounded ones.