Rounding problem

[ShawnLerner]

This may be more of a math 101 question than an Excel question but I would be grateful for some help in any case.

Here is a simplified example of the problem:

Say you have 12 widgets to divide up amongst 3 people and each one is non divisible (has to be whole numbers). The distribution percentage of each is governed by a formula which tells you Person A gets 47%, Person B gets 23%, Person C gets 30%. Therefore Person A should have (12*47%)=5.64 widgets, B=2.76, C=3.6.

So, I can't just round to whole numbers because then A=6, B=3, C=4 for a total of 13 not 12. What's a formula to prorate them properly?

Thanks

 

[tygrrboi]

Is it fine to have widgets remaining? Or do they need to be given to the person with the decimal place closest to 1? (eg 5.64 gets 6, 2.76 gets 3, and 3.6 gets 3)

 

[FDibbins]

easiest way to do this, is to decide who gets the "remaining balance", then apply the given % to the other 2, then subtract their total from the grand total.

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

5​

[/td][td]Qty[/td][td]

12​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]A[/td][td]

47%​

[/td][td]

5​

[/td][/tr]

[tr][td]

7​

[/td][td]B[/td][td]

23%​

[/td][td]

2​

[/td][/tr]

[tr][td]

8​

[/td][td]C[/td][td][/td][td]

5​

[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]

12​

[/td][/tr]
[/table]
C6=ROUNDDOWN($B$5*B6,0)
C7=ROUNDDOWN($B$5*B7,0)
C8=B5-C6-C7
C9=SUM(C6:C8)

 

[tygrrboi]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]47%[/TD]
[TD="align: center"]5.64[/TD]
[TD="align: center"]0.64[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]23%[/TD]
[TD="align: center"]2.76[/TD]
[TD="align: center"]0.76[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"]3.6[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

B2 = $B$1 * A2 copy down
C2 = MOD(B2, 1) copy down

D2 = {INT(B2)+IF(RANK(C2,$C$2:$C$4)<=($B$1-SUM(INT($B$2:$B$4))), 1, 0)}
Input with Control Shift Enter and not just enter to make an array function. {} Curly braces appear automatically if done correctly. Do not manually put in the curly braces.

Drag down

 

[ShawnLerner]

Thanks for the suggestion but this doesn't really work because in the actual example there are way more than 3, so rounding all of them down but the remainder gives you numbers that are way off. Sorry, should have specified that in the OP.

easiest way to do this, is to decide who gets the "remaining balance", then apply the given % to the other 2, then subtract their total from the grand total.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Qty[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]A[/TD]
[TD]

47%​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]B[/TD]
[TD]

23%​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

12​

[/TD]
[/TR]
</tbody>[/TABLE]

C6=ROUNDDOWN($B$5*B6,0)
C7=ROUNDDOWN($B$5*B7,0)
C8=B5-C6-C7
C9=SUM(C6:C8)

 

[ShawnLerner]

For some reason I couldn't get this to work. I'm sure I did something wrong.

I came up with another solution. Not quite as eloquent as yours but it seemed to do the trick. FYI, here's what I did:

On row 1 I put the round down number for each person
Then put the remainder in the cell below it
Then ranked the remainders for each person from highest to lowest
Then took the total number of widgets minus the sum of the round down numbers.
Then I said if remainder = 3, add 1 to each of the round down numbers. If remainder 2, add one to each of the round down numbers ranked 1 or 2. If remainder = 1, add 1 to the 1st ranked, etc.

Unrelated question: how do you make those tables in your responses?

 

[ShawnLerner]

I am reffering to tygrrboi's solution.Yesterday, 08:

 

[tygrrboi]

On row 1 I put the round down number for each person
Then put the remainder in the cell below it
Then ranked the remainders for each person from highest to lowest
Then took the total number of widgets minus the sum of the round down numbers.
Then I said if remainder = 3, add 1 to each of the round down numbers. If remainder 2, add one to each of the round down numbers ranked 1 or 2. If remainder = 1, add 1 to the 1st ranked, etc.

That is essentially what my formula did. I just cut out a lot of the helper columns by doing the work in one cell. The reason why I had column C is I couldn't figure out how to use the RANK function with remainders of each cell in an array for the second variable... Otherwise I would have put it all into one formula.

I'm glad it works for you anyway. There is no harm in helper columns (or rows). Hide them if you do not want them visible... it's unlikely you'll run out of space on a sheet.

To answer your unrelated question:
In the message window there is a blue-ish looking table object. Click that, tell it how many rows and columns you want to insert, Choose the border you want, and it puts your table in the message. You can add or delete rows and columns using the other icons in that set. Just type your information into the table and you're done.
(If you cannot see the table object/icon, you might have to click the Advanced Editor button on the bottom near send...)

 

[ShawnLerner]

Thanks again for the help. I see how to make a table now. It only appears when you hit "Go Advanced" not "Post quick reply".