L_COMBINR
=L_COMBINR(array, k)
| Argument | Description | Example |
|---|---|---|
| array | An n x 1 array of text or numeric values | {"A";42;"B";0;"c"} |
| k | The number of values in each combination | 3 |
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.
L_COMBINR returns all the combinations of values from an array of length n, chosen k at a time. The "R" in L_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 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 L_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.
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Return all the combinations of (N Choose K) from an Nx1 array */ L_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) )))) ));
Named Function for Google Sheets
This code isn't formatted well, but GS doesn't have a good editor for their named functions.
Name: L_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))) )))))
L_COMBINR Examples
Test: Copy and Paste this LET function into a cell =LET( array, {"3♠";"Q♣";"10♦";"9♥";"J♦"}, k, 3, L_COMBINR(array,k) ) Result: (see image above)
