How to get to a specific value with rounding...

[largeselection]

I'm wondering how this might be possible either via VBA or fancy formula work...

Essentially I'd like to get to a specific value when using a percent to total calculation, but because of rounding I'm slightly off...is there any way to make sure we get to the specific value?

For Example:

I want to divide 55 coins among 4 people based on the below percentages:
Person1 = 10%
Person2 = 20%
Person3 = 30%
Person4 = 40%

When I round in Excel I get
10% * 55 = 5.5 which rounds to = 6
20% * 55 = 11
30% * 55 = 16.5 which rounds to = 17
40% * 55 = 22

6+11+17+22 = 56 which is 1 more than 55.

Ideally, I would like the formula or VBA take the 1 unit from the highest recipient or x number of highest recipients. IE - in another example, if I am over by 3 units then I would want to take 1 unit each from the top 3 recipients.

Does this make sense?

Any ideas how to accomplish this?

Thanks in advance!

 

[RonB1111]

This formula places any rounding error in the highest percentage. Enter 55 in A1. Enter .1, .2, .3, .4 in B2 thru B5 respectively. Enter this formula in C2 and copy down:

=ROUND(A$1*B2,0)+IF(MAX(B$2:B$5)=B2,A$1-SUMPRODUCT(ROUND(B$2:B$5*A$1,0)),0)

 

[largeselection]

Awesome I will play with this and see if I can get it to work for what I'm looking for!

Is there any way to modify this so that it spreads among the top x recipients rather than just the very top 1?
So for example, say there are 10 people and the rounding comes out to 5 coins more than originally available, is it possible to modify this so that the top 5 people would get 1 less coin each rather than the very top person getting 5 less coins?

 

[RonB1111]

Not off the top of my head. My experience is the rounding errors usually are less than 1. Can you give an example where the rounding difference could be 5?

 

[largeselection]

I can give a specific example where the rounding is = +2, I'm sure I could find a +5 as I expand the number of people getting portions and thus their percent to totals decrease, but an example of 2 (since it is easier to type out) is below:

195 Coins to distribute

Person1 = 10% = 19.5 = 20
Person2 = 11% = 21.45 = 21
Person3 = 20% = 39
Person4 = 30% = 58.5 = 59
Person5 = 9% = 17.55 = 18
Person6 = 5% = 9.75 = 10
Person7 = 13.65 = 14
Person 8 = 15.6 = 16

Total = 197 (+2)

I have a solution that seems to work, but it's fairly cumbersome hence why I figured I'd ask if anyone has a more elegant solution.

Essentially the solution that I've been using just assigns a non-repeating rank based on the preliminary distribution, then if the rank < the number of units over/under it adds/subtracts 1 unit from that person.

So in the example above, persons 3 and 4 would be assigned rank 1 and 2 and since their rank is <= the 2 units that I am over then they would each lose 1 unit and thus the total would be exact.

 

[RonB1111]

This requires a helper column but I think it gives your desired results. Enter 195 in A1. Enter your %'s in B2 thru B9 respectively.
Enter this helper formula in C2 and copy down (it ranks the %'s adjusting for ties):

=RANK(B2,B$2:B$9,1)+(ROW()*0.000001)

Now enter this in D2 and copy down:

=ROUND(A$1*B2,0)+IF(RANK(C2,C$2:C$9,0)<=A$1-SUMPRODUCT(ROUND(B$2:B$9*A$1,0)),1,IF(RANK(C2,C$2:C$9,0)<=ABS(A$1-SUMPRODUCT(ROUND(B$2:B$9*A$1,0))),-1,0))

 

[largeselection]

Very nice! I think that works so I will try and incorporate that into my sheet.

Thanks!!!

 

[RonB1111]

You're welcome!

 

[West Man]

Another approach if data can be sorted ascending by percentage

[TABLE="width: 270"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"]195[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person6 [/TD]
[TD="align: right"]4.85%[/TD]
[TD="align: right"]9.4575[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Person5 [/TD]
[TD="align: right"]8.00%[/TD]
[TD="align: right"]15.6[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Person1 [/TD]
[TD="align: right"]9.50%[/TD]
[TD="align: right"]18.525[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Person2 [/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Person7 [/TD]
[TD="align: right"]11.65%[/TD]
[TD="align: right"]22.7175[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Person 8 [/TD]
[TD="align: right"]16.00%[/TD]
[TD="align: right"]31.2[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]Person3 [/TD]
[TD="align: right"]18.00%[/TD]
[TD="align: right"]35.1[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Person4 [/TD]
[TD="align: right"]22.00%[/TD]
[TD="align: right"]42.9[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100.00%[/TD]
[TD] [/TD]
[TD="align: right"]195[/TD]
[/TR]
</tbody>[/TABLE]
D2 and copied down =INT(A$1*B2)+(ROWS(A$2:A2)>=$B$1-MOD($A$1,$B$1))