I've got an integer number that I want to split into six different integers so that their proportions match given weights as closely as possible. Example below:
What's the cleanest way to do it? I've done it using a complicated combination of ROUNDDOWN and RANK, but it's not 100% fool-proof.
Okay, this may be slightly overcomplicated, but:
=LET(total,$A$2,weights,$B$2:$B$7,valTbl,MAKEARRAY(COUNTA(weights),2,LAMBDA(y,x,IF(x=1,y,total*INDEX(weights,y)/SUM(weights)))),sortTbl,SORTBY(valTbl,MOD(INDEX(valTbl,,2),1),-1),lowtotal,SUM(ROUNDDOWN(INDEX(valTbl,,2),0)),outTbl,MAKEARRAY(COUNTA(weights),2,LAMBDA(y,x,ROUNDDOWN(INDEX(sortTbl,y,x),0)+IF(AND(x=2,y<=(total-lowtotal)),1,0))),INDEX(SORT(outTbl,1,1),,2))
(Due to using the LAMBDA
and MAKEARRAY
functions, this will not work in older version of Excel)
How it works:
First, it creates the valTbl
array, which contains the Row Number and the exact proportional value (including decimals)
Row | Value |
---|---|
1 | 134.359 |
2 | 87.33333 |
3 | 80.61538 |
4 | 53.74359 |
5 | 26.87179 |
6 | 403.0769 |
Then it sorts this, Descending, by the decimal portion of the value into sortTbl
:
Row | Value |
---|---|
5 | 26.87179 |
4 | 53.74359 |
3 | 80.61538 |
1 | 134.359 |
2 | 87.33333 |
6 | 403.0769 |
It sums up the Integer portions of the values (783), subtracts that from the total (786-783 = 3) which we will temporarily refer to as 𝕐, then creates the new outTbl
by taking the Integer portions of the values, and adding 1 to the first 𝕐 entries:
Row | Value |
---|---|
5 | 27 (+1) |
4 | 54 (+1) |
3 | 81 (+1) |
1 | 134 (+0) |
2 | 87 (+0) |
6 | 403 (+0) |
Finally, we sort this, Ascending, by the Row, (which puts it back into the original order) and output only the Value column:
Result |
---|
134 |
87 |
81 |
54 |
27 |
403 |
Due to the way this works, this even functions for cases such as when our Weights result in matching Decimal Portions: (This prioritises earlier rows, but you could extend the SortBy
to change that if you wanted to prioritise larger or smaller Integer values or something)
Number | Weight | → | Row¹ | Value¹ | → | Row² | Value² | → | Row³ | Value³ | → | Result |
---|---|---|---|---|---|---|---|---|---|---|---|---|
100 | 3 | → | 1 | 33.3333 | → | 1 | 33.3333 | → | 1 | 34 (+1) | → | 34 |
3 | → | 2 | 33.3333 | → | 2 | 33.3333 | → | 2 | 33 (+0) | → | 33 | |
3 | → | 3 | 33.3333 | → | 3 | 33.3333 | → | 3 | 33 (+0) | → | 33 |
or even
Number | Weight | → | Row¹ | Value¹ | → | Row² | Value² | → | Row³ | Value³ | → | Result |
---|---|---|---|---|---|---|---|---|---|---|---|---|
100 | 1 | → | 1 | 8.3333 | → | 1 | 8.3333 | → | 1 | 9 (+1) | → | 9 |
4 | → | 2 | 33.3333 | → | 2 | 33.3333 | → | 2 | 33 (+0) | → | 33 | |
7 | → | 3 | 58.3333 | → | 3 | 58.3333 | → | 3 | 58 (+0) | → | 58 |