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

 

[SpillerBD]

Can you sort the items from smallest to greatest? Then any rounding error introduced would more easily be included in the last value where its significance would be lessened.
Of course it is best to round only at the end of all calculations. These rounded results shouldn't be used for further calculations down-stream, but the exact figures. (of course 'exact' in Excel, decimal to binary to decimal conversion/rounding issues may still prevent your exact sum of 100%)

(What was I reading about significant digits and those that grew up with slide-rules...?)

 

[dgarland]

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.

My problem is that the values do not total 100%. Each individual value is referenced elsewhere, and the rounded numbers (to two decimal places) cause problems if they total more than 100%. I cant just use the sum of the actual figures because I dont really care about the sum. I care about the individual values.

 

[dgarland]

Can you sort the items from smallest to greatest? Then any rounding error introduced would more easily be included in the last value where its significance would be lessened.
Of course it is best to round only at the end of all calculations. These rounded results shouldn't be used for further calculations down-stream, but the exact figures. (of course 'exact' in Excel, decimal to binary to decimal conversion/rounding issues may still prevent your exact sum of 100%)

(What was I reading about significant digits and those that grew up with slide-rules...?)

That is the idea, and I have actually had some limited success by using that logic. The issue that is arising is that if one value is much higher than others, it can split things up in a bad way. For example:
series is 67,24,24,24,24,24,24, and percentages are "31.7536%", and 6 "11.3744%" this rounds to 31.75 and 11.37x6. This means 99.97%. I COULD apply the .03% to the largest position, and call it good. The problem is, if the data changes so that there is a .03% (or even higher) difference, and all values are relatively close to each other, then you are better off spreading the adjustment across several values in the range.

I am surprised there isnt a function in Excel to grab a range of percentages and round so the total matches 100% and minimizes absolute drift from exact values.

 

[dgarland]

I think I have a solution. I went through a couple iterations, and Method3 (blue) does pretty much what I need it to do:

 

[dgarland]

Imgur: The most awesome images on the Internet