Website last updated: 03.09.2024
For permutations and combinations, Excel provides functions called "PERMUT", "COMBIN" and "COMBINA" that can be used to calculate how many possibilities there are for the different cases.
Unfortunately, Excel does not provide a function that automatically generates these permutations or combinations and displays them on the worksheet.
Here we will introduce a function programmed in VBA (UDF = User Defined Function) called "PermComb", which automatically generates permutations and combinations.
* * *
The function 'PermComb' generates optional permutations or combinations of length k from a basic set with n elements.
Since the function returns multiple values, it is an array formula. If you are using Excel 2019 or an older version, you must - as is usual with CSE formulas - select a suitable range before entering the formula and complete the entry of the formula with CTRL-SHIFT-ENTER.
A basic introduction to array formulas can be found here:
https://hermann-baum.de/excel/tutorial/array_formulas/
The Excel file contains the VBA code of the function 'PermComb' and examples of using the function.
The Excel file is free, without advertising, everything is openly visible and freely changeable.
= PermComb ( basic set; [k]; [order]; [repetition]; [index] )
| argument | description |
|---|---|
| basic set | Can be a range (1 column or 1 row) or a one-dimensional array; n = number of elements in the base set |
| k (optional) |
Length of permutation/combination (number of items to select) (default: k = n) |
| order (optional) |
FALSE (0): no consideration of the order TRUE (1): taking into account the order (default) |
| repetition (optional) |
FALSE (0): without repetitions (default) TRUE (1): with repetitions |
| index (optional) |
Possible options:
If this parameter is missing, all permutations/combinations are generated, otherwise only the selection determined by the indices. |
For all examples the base set consists of the 6 letters ABCDEF.
These 6 letters are in cells B1:B6.
1. All permutations of the 6 letters ABCDEF:
= permcomb (B1:B6)
Only the first parameter is specified. All others use the default values.
A B C D E F
A B C D F E
A B C E D F
. . .
F E D C B A
There are 720 permutations.
2. All k-permutations without repetition with 4 elements from the 6 letters ABCDEF:
= permcomb (B1:B6, 4)
A B C D
A B C E
A B C F
. . .
F E D C
There are 360 k-permutations.
3. All combinations with repetition of 4 elements from the 6 letters ABCDEF:
= permcomb (B1:B6, 4, FALSE, TRUE)
A A A A
A A A B
A A A C
. . .
F F F F
There are 126 combinations.
4. Like the last example, but only the first two and the last two combinations:
= permcomb (B1:B6, 4, FALSE, TRUE, {1,2,125,126})
A A A A
A A A B
E F F F
F F F F
Only these 4 combinations are generated and displayed.
* * *
What is the difference between permutations, k-permutations and combinations?
In general, the order matters for permutations and k-permutations, but not for combinations.
Permutations are different arrangements (sequences) of n elements (Latin permutare = swap).
For example, there are 120 ways to arrange five books next to each other in different orders. So there are 120 permutations of these 5 elements.
In combinatorics, a k-permutation is a selection of k elements from a basic set of n elements taking the order into account.
For example, you select 4 books from 6 books and place them next to each other in a certain order.
If repetitions are permitted when selecting the k elements, we speak of k‑permutations with repetition,
otherwise of k‑permutations without repetition.
When selecting the 4 books, it is conceivable that a certain book was selected several times (e.g. for reading).
A combination is a selection of k elements from a basic set of n elements without taking the order into account.
For example, you select 4 books from 6 books, and it is only of interest which 4 books they are.
There are combinations with repetitions and without repetitions. A combination with repetition would be if, when choosing four books, you chose book A three times and book C once, but the order does not matter.
A permutation is therefore a k-permutation without repetition in the special case k = n.
One can therefore distinguish four basic cases (ORD = order, REP = repetition):
Selection k from n with ORD without REP
(k-Permutationen without REP)
Number:
Excel formula: =PERMUT(n;k)
Selection k from n with ORD with REP
(k-Permutationen with REP)
Number:
Excel formula: =n^k
Selection k from n without ORD without REP
(combinations without REP)
Number:
Excel formula: =COMBIN(n;k)
Selection k from n without ORD with REP
(combinations with REP)
Number:
Excel formula: =COMBINA(n;k)
* * *
The Example 4 on the third worksheet of the download file is intended to show how the parameter 'index' can be used effectively.
This example 4 is about combining two different basic sets, so to speak, to form combinations of combinations.
The first basic set consists of the two letters A and B.
There are 4 k-permutations with repetition of these two letters:
AA
AB
BA
BB
Formula to generate these k-permutations:
=permcomb(S5:S6,2,,TRUE) (1)
The second basic set consists of the two digits 1 and 2.
There are 8 k-permutations with repetition of three digits from this basic set:
1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 1
2 2 2
Formula to generate these k-permutations:
=permcomb(U5:U6,3,,TRUE) (2)
Now all possible combinations of these two "sets of permutations" are to be formed, i.e. all combinations of two letters (only A and B) and three numbers (only 1 and 2) are required:
A A 1 1 1
A A 1 1 2
...
B B 2 2 2
There are 4 x 8 = 32 of these combinations.
The first eight combinations all start with "AA", the second eight with "AB" and so on.
In the formula for generating the group of letters - see formula (1) - we would have to ensure that the index 1 is used eight times, then the index 2 eight times and so on.
Such a sequence of indices produces the formula
=QUOTIENT(ROW(1:32)-1,8)+1 (3)
For the group of digits generated with formula (2), we would need indices that first run from 1 to 8, then start again at 1 and run to 8, and so on.
Such a sequence of indices produces the formula
=MOD(ROW(1:32)-1,8)+1 (4)
In columns AH and AI of the third worksheet, the letter group is now created by inserting the formula (3) as the 'Index' parameter in the formula (1):
=permcomb(S5:S6,2,,TRUE, QUOTIENT(ROW(1:32)-1,8)+1)
In columns AJ, AK and AL, the group of digits is created by inserting the formula (4) as the parameter 'Index' in the formula (2):
=permcomb(U5:U6,3,,TRUE, MOD(ROW(1:32)-1,8)+1)
If you want to have the 32 combinations in one cell each, you can then do this with a formula like
=AH5 & AI5 & AJ5 & AK5 & AL5.
* * *
Sometimes you want to find all possible arrangements of a basic set in which not all elements can be distinguished from one another. As an example, consider the basic set {A, B, B, C, C, C} (see the following figure).
The idea behind the solution is to first generate all 720 permutations using the PermComb function - as if there were 6 distinguishable elements - and then use the UNIQUE function to remove all duplicates.
The formula used is therefore:
=UNIQUE(permcomb(B2:G2))
If you are using an older version of Excel that does not know the UNIQUE function, you can download a corresponding UDF (user-defined function) 'Unique' ▸here.
There are 60 permutations of this basic set. The general formula for the number is:
Specifically for this example:
This example is not included in the download file.
