PERMUTATIONS
=PERMUTATIONS(array_1, array_2)
| Argument | Description | Example |
|---|---|---|
| array | A column vector of numbers or text | {"A";"42";"d"} |
| n_chosen | A whole number greater than 0. | 3 |
In the template file, navigate to the Arrays worksheet to see the PERMUTATIONS function in action.
Description
The PERMUTATIONS function returns a table of all permutations of the values in the given array, choosing n at a time with repetition. This function corresponds to the built-in PERMUTATIONA(number,n_chosen) function in Excel, but it returns the actual permutations rather than just the total number.
PERMUTATIONS works with both text and numeric values (as well as errors and boolean values). Blank values in the given array will become 0.
A classic example is finding all possible series of N coin flips. The array consists of two values: Heads and Tails. The example below shows all the permutations of Heads and Tails, choosing to flip 4 times.
The total number of rows returned by PERMUTATIONS(array,n_chosen) is given by PERMUTATIONA(ROWS(array),n_chosen) which is calculated as ROWS(array)^n_chosen.
PERMUTATIONS is actually just a nested COMBINATIONS function:
PERMUTATIONS(array,3) =COMBINATIONS(array, COMBINATIONS(array,array) )
If array is a row vector, it is first converted to a column vector. If array has multiple columns AND rows, then each row is treated as a single object, so the number of columns in the final returned array would be n_chosen*columns_in_array.
Lambda Formula
This code for using PERMUTATIONS 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.
Important: This function also requires COMBINATIONS, REPELEM and REPARRAY
Code to Create Function via the Name Manager
Name: PERMUTATIONS Comment: Return all permutations of an array, chosen n at a time with repetition Refers To: =LAMBDA(array,n_chosen, LET(doc,"https://www.vertex42.com/lambda/permutations.html", array,IF(AND(COLUMNS(array)>1,ROWS(array)=1),TRANSPOSE(array),array), REDUCE("",SEQUENCE(n_chosen-1),LAMBDA(acc,i, IF(i=1,COMBINATIONS(array,array),COMBINATIONS(array,acc)) )) ))
Named Function for Google Sheets
Name: PERMUTATIONS Description: Return all permutations of an array, chosen n at a time with repetition Arguments: array, n_chosen Function: Not Available Yet
PERMUTATIONS Examples
See Also
COMBINATIONS, REPARRAY, REPELEM, COMBINR
