Create an array that enumerates different combinations of percentages.

[CEdward940]

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 isn't manual?

Here are a few combinations that I have created below:

A	B	C	D	E	F	G	H	I	J	K	L	M
100%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
95%	5%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
90%	5%	5%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
85%	5%	5%	5%	0%	0%	0%	0%	0%	0%	0%	0%	0%
80%
75%
70%
65%
0%	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
0%	0%	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
0%	0%	0%	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%
0%	0%	0%	0%	100%	0%	0%	0%	0%	0%	0%	0%	0%
0%	0%	0%	0%	0%	100%	0%	0%	0%	0%	0%	0%	0%
0%	0%	0%	0%	0%	0%	100%	0%	0%	0%	0%	0%	0%
0%	0%	0%	0%	0%	0%	0%	100%	0%	0%	0%	0%	0%
0%	0%	0%	0%	0%	0%	0%	0%	100%	0%	0%	0%	0%
0%	0%	0%	0%	0%	0%	0%	0%	0%	100%	0%	0%	0%
0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	100%	0%	0%
0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	100%	0%
0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	100%

As you can see I have used increments of 5%. Ideally, you will also see combinations where each letter might have 10%, 15%, 20%, etc. So another (random) combination would be:

60% 10% 15% 5% 10% 0% 0% 0% 0% 0% 0% 0% 0%

Really looking forward to anyone's input.

[#2]

@CEdward940 

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)
)

[#3]

@Patrick2788 

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?

[#4]

The 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.

[#5]

Is 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).

[#6]

@CEdward940 

You could use Solver.  It will give you 1 solution at a time but you can keep running it and it won't run into memory issues.