Hi Friends,
Wonder if anyone could help with this proportional distribution function.
A - is a set number in a cell, 55 in this case.
B - I have 19 (1 to 19) workdays in a row
C - I have % distribution ratio of each of the workdays which totals to 100%
D - Using straightforward formula of multiplying distribution ratio to a set number (C x A$) I could get proportional distribution but it is decimals, however I need rounded distribution.
E - Using =ROUND(C x A$),0) give me rounded numbers for each workday, however the total of 19 workdays go to 59, which is understandable because the formula rounded the numbers.
However, what I would like to achieve is have rounded numbers in E but somehow cap the sum of all workdays to A (55), is this possible at all?
I know a way where I can separate the trunc and integers and then apply ranking etc, but that won't work in my scenario as number of rows I will be working with will be in thousands. So any help in achieving this with a magic excel formula would be highly appreciated!
Attached is the mini excel spreadsheet with the above illustration and also attaching a screenshot below.
Wonder if anyone could help with this proportional distribution function.
A - is a set number in a cell, 55 in this case.
B - I have 19 (1 to 19) workdays in a row
C - I have % distribution ratio of each of the workdays which totals to 100%
D - Using straightforward formula of multiplying distribution ratio to a set number (C x A$) I could get proportional distribution but it is decimals, however I need rounded distribution.
E - Using =ROUND(C x A$),0) give me rounded numbers for each workday, however the total of 19 workdays go to 59, which is understandable because the formula rounded the numbers.
However, what I would like to achieve is have rounded numbers in E but somehow cap the sum of all workdays to A (55), is this possible at all?
I know a way where I can separate the trunc and integers and then apply ranking etc, but that won't work in my scenario as number of rows I will be working with will be in thousands. So any help in achieving this with a magic excel formula would be highly appreciated!
Attached is the mini excel spreadsheet with the above illustration and also attaching a screenshot below.
| Proportional Distribution Rounding.xlsx | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 2 | A | Total volume | 55 | |||||||||||||||||||||
| 3 | ||||||||||||||||||||||||
| 4 | B | Workday | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | |||
| 5 | C | Distribution Ratio | 5.00% | 3.33% | 6.67% | 3.33% | 1.67% | 5.00% | 8.33% | 5.00% | 6.67% | 6.67% | 3.33% | 1.67% | 5.00% | 3.33% | 5.00% | 3.33% | 6.67% | 8.33% | 11.67% | 100.00% | ||
| 6 | D | Distribution (Raw) | 2.75000 | 1.83333 | 3.66667 | 1.83333 | 0.91667 | 2.75000 | 4.58333 | 2.75000 | 3.66667 | 3.66667 | 1.83333 | 0.91667 | 2.75000 | 1.83333 | 2.75000 | 1.83333 | 3.66667 | 4.58333 | 6.41667 | 55.00000 | ||
| 7 | E | Distribution (Rounded) | 3.00000 | 2.00000 | 4.00000 | 2.00000 | 1.00000 | 3.00000 | 5.00000 | 3.00000 | 4.00000 | 4.00000 | 2.00000 | 1.00000 | 3.00000 | 2.00000 | 3.00000 | 2.00000 | 4.00000 | 5.00000 | 6.00000 | 59.00000 | ||
Sheet1 | ||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C6:U6 | C6 | =C5*$C$2 |
| V6:V7 | V6 | =SUM(C6:U6) |
| C7:U7 | C7 | =ROUND(C5*$C$2,0) |
