Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Create an array that enumerates different combinations of percentages.

Copper Contributor
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:

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

 

 

 
5 Replies

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

 

@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?

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

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

 

Patrick2788_0-1697827221860.png