COMBINR

=COMBINR(array, k)

Download the Template

In the template file, navigate to the Arrays worksheet to see the COMBINR function in action.

Description

Excel has a COMBIN(n,k) function which will tell you the total number of combinations of n values chosen k at a time. This is the same as the binomial coefficient (n,k) or "n choose k" where COMBIN=n!/((n-k)!k!) or COMBIN=FACT(n)/(FACT(n-k)*FACT(k)). However, COMBIN does not return an array of those combinations.

COMBINR returns all the combinations of values from an array of length n, chosen k at a time. The "R" in COMBINR refers to the function returning all the combinations of "n choose k." The returned array is size m x k where m=COMBIN(n,k) is the number of rows. Each row is a combination of the k elements of the array. The values of the array can be text or numbers.

I created this formula while working on a solution for the "Cribbage" case which premiered during the 2023 season of the Microsoft Excel Esports. The case involves finding all possible combinations of cards in your hand taken 2, 3, 4, or 5 at a time.

More about Excel Esports!

How it Works

The algorithm uses recursion, but only requires the REDUCE function to gradually add one row at a time to the accumulator. It creates an array of indices first, and then replaces that array of indices with the corresponding original array values.

The first row of indices is SEQUENCE(1,k) or {1,2,3} in this example. The algorithm then increments the indices one at a time from the right to the left until the maximum allowable value is reached. The maximum value for each column is SEQUENCE(1,k,n-k+1,1) or {3,4,5} in this example.

While fairly easy to do this by hand, implementing the algorithm in Excel can be tricky. The trick is figuring out which column should be incremented each time by comparing the previous row to the maximum (to see which values have reached the maximum). Whenever a value is incremented, all of the values to the right become a counting sequence.

Lambda Formula

This code for using COMBINR in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Return all the combinations of (N Choose K) from an Nx1 array
*/
COMBINR = LAMBDA(array,k,
LET(doc,"https://www.vertex42.com/lambda/combinr.html",
    array,IF(AND(COLUMNS(array)>1,ROWS(array)=1),TRANSPOSE(array),array),
    cols,COLUMNS(array),
    n,ROWS(array),
    IF(cols>1,"Error: Array size must be n x 1",
    IF(n<3,"Error: n must be >= 3",
    IF(k>n,"Error: k must be < Rows",
    LET(
    m,COMBIN(n,k),
    ms,SEQUENCE(m),
    mx,SEQUENCE(1,k,n-k+1,1),
    combos,REDUCE(SEQUENCE(m,k,0,0),ms,
    LAMBDA(acc,i,
        IF(i=1,SEQUENCE(1,k),
        LET(prev_row,INDEX(acc,i-1,0),
            tf,prev_row=mx,
            col2inc,IFERROR(MATCH(TRUE,tf,0),k+1)-1,
            new_row,IF(col2inc=1,
                SEQUENCE(1,k-col2inc+1,INDEX(prev_row,1,col2inc)+1),
                HSTACK(
                    CHOOSECOLS(prev_row,SEQUENCE(1,col2inc-1)),
                    SEQUENCE(1,k-col2inc+1,INDEX(prev_row,1,col2inc)+1)
                )
            ),
            VSTACK(acc,new_row)
        ))
    )),
    INDEX(array,combos)
    ))))
));

Name: COMBINR
Description: Return all the combinations of (N Choose K) from an nx1 array
Arguments: array, k
Function:

=LET(doc,"https://www.vertex42.com/lambda/combinr.html",    array,IF(AND(COLUMNS(array)>1,ROWS(array)=1),TRANSPOSE(array),array),
cols,COLUMNS(array),
n,ROWS(array),
IF(cols>1,"Error: Array size must be n x 1",
IF(n<3,"Error: n must be >= 3",
IF(k>n,"Error: k must be < Rows",
LET(m,COMBIN(n,k),
ms,SEQUENCE(m),
mx,SEQUENCE(1,k,n-k+1,1),
combos,REDUCE(SEQUENCE(m,k,0,0),ms,LAMBDA(acc,i,
IF(i=1,SEQUENCE(1,k),
LET(prev_row,INDEX(acc,i-1,0),
tf,ARRAYFORMULA(prev_row=mx),
col2inc,IFERROR(MATCH(TRUE,tf,0),k+1)-1,
new_row,IF(col2inc=1,
SEQUENCE(1,k-col2inc+1,INDEX(prev_row,1,col2inc)+1),
HSTACK(CHOOSECOLS(prev_row,SEQUENCE(1,col2inc-1)),
SEQUENCE(1,k-col2inc+1,INDEX(prev_row,1,col2inc)+1)
)),VSTACK(acc,new_row))))),
MAP(combos,LAMBDA(cell,INDEX(array,cell)))
)))))

COMBINR Examples

Test: Copy and Paste this LET function into a cell
=LET(
    array, {"3♠";"Q♣";"10♦";"9♥";"J♦"},
    k, 3,
    COMBINR(array,k)
)

Result: (see image above)

See Also

REPARRAY, REPELEM, COMBINATIONS