Forum Discussion

CEdward940's avatar
CEdward940
Copper Contributor
Oct 19, 2023

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

 

 

 
Excel on Mac
Formulas and Functions
office 365

5 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 19, 2023

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

     

    • CEdward940's avatar
      CEdward940
      Copper Contributor
      Oct 19, 2023

      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?

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Oct 20, 2023
        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.

Resources