Forum Discussion
CEdward940
Oct 19, 2023Copper Contributor
Create an array that enumerates different combinations of percentages.
Hello, I would like to create an array that enumerates all of the different ways in which 100% can be split up between 13 different letters. Is there a quick and error-free way to do this that is...
Patrick2788
Oct 19, 2023Silver Contributor
This request has appeared on this site several times in a few different formats. To solve with a formula it's a matter of generating combinations and then selecting combinations that = target (1 in this case).
From the start I think you have to take repeats off the table for memory considerations.
For example, if your increment is .05 then the possible combinations (Equaling 1 and otherwise) is determined by:
=SUM(COMBINA(20,SEQUENCE(13)))
=573,166,439
This solution does not use any repeats:
=LET(
letters, 13,
header, CHAR(SEQUENCE(, letters, 65)),
step, 0.02,
step_array, SEQUENCE(, 100, step, step),
BinMatrix, MOD(
INT(SEQUENCE(2 ^ letters, , 0) / 2 ^ SEQUENCE(, letters, 0)),
2
),
StepMatrix, TAKE(BinMatrix * TOROW(step_array), , 13),
Combinations, FILTER(
StepMatrix,
MMULT(StepMatrix, SEQUENCE(letters, , 1, 0)) = 1
),
VSTACK(header, Combinations)
)
- CEdward940Oct 19, 2023Copper Contributor
Thanks Patrick. So I looked at your solution and I changed the increment to 0.05. I notice among the solutions, however, that some rows that I would expect to be there aren't. For example, the rows where each letter is 100% (and every other letter is 0%). Then there are rows where I expect a letter to be, say, 75% and another letter is 25%.
Am I missing something?
- Patrick2788Oct 20, 2023Silver ContributorThe method the formula uses to generate the Bin Matrix is taking the number of letters (13) and using that for the width of the array. The problem with 13 is it's not enough to accommodate the 20 increments for .05 where 1/.05 (.05, .10, .15, etc.). When the width is fixed at 20 (essentially replacing 'letters' with 20), it produces memory errors. Maybe it needs some more tinkering but it looks like a memory/calculation issue.
- CEdward940Oct 20, 2023Copper ContributorIs there an alternative way to do this then?
Even when I increase the increment to 10% to create far fewer possible combinations, I still don't see combos I expect to see (e.g. those combinations that assign 100% to a single letter).