How to use the PERMUTATIONA function
What is the PERMUTATIONA function?
The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a larger group of elements. Repetition is allowed.
Table of Contents
1. Introduction
Explain what a permutation is?
A permutation is a way of arranging a set of elements in a specific order. For example, if you have three letters A, B, and C, you can arrange them in six different ways: ABC, ACB, BAC, BCA, CAB, and CBA.
Each of these arrangements is called a permutation of the three letters. The order of the objects matters in a permutation, so ABC and BAC are considered different permutations.
What is repetition allowed?
Repetition allowed is when there can be duplicate values. A phone number is a great example of a permutation that may contain duplicate digits.
What is an element?
An element refers to an item which can be anything.
What is the difference between the PERMUTATIONA function and the PERMUT function?
PERMUTATIONA allows repetition, PERMUT does not.
PERMUTATIONA calculates the number of permutations for a given number of objects (n) selected from a pool of objects where order is important and objects can repeat.
PERMUT calculates the number of permutations for a given number of objects (n) selected from a pool of unique objects where order is important but repetition is not allowed.
How can I create a list of permutations with repetition?
Recommended articles
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
What is the difference between combination and permutation?
Combinations: Order does not matter. ABC and BAC are considered the same.
Permutations - Order matters. ABC is different from BAC.
Permutations are used when order matters - like sequences, orders, arrangements.
Combinations are used when specific groupings matter but order doesn't - like teams, packs, sets.
How can I create a list of permutations without repetition?
Recommended articles
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
Related functions
Excel Function and Arguments | Description |
---|---|
PERMUT(number, number_chosen) | Returns the number of permutations for a given number of objects selected from number items without repetition |
PERMUTATIONA(number, number_chosen) | Returns the number of permutations for a given number of objects selected from number items with repetition allowed |
COMBIN(number, number_chosen) | Returns the number of combinations for a given number of objects selected from number items without repetition |
COMBINA(number, number_chosen) | Returns the number of combinations for a given number of objects selected from number items with repetition allowed |
2. PERMUTATIONA Function Syntax
PERMUTATIONA(number, number-chosen)
3. PERMUTATIONA Function Arguments
number | The total number of elements. |
number-chosen | A specific number of elements. |
4. PERMUTATIONA function Example
A jewelry designer is creating a new bracelet design with two different gemstones. The available gemstones are ruby, emerald, and sapphire. If the order of the gemstones matters and repetition is allowed, how many different bracelet designs can be created?
The picture above shows permutations with repetition allowed, 2 out of 3 gemstones.
Formula in cell D3:
The formula returns 9 different bracelet designs can be created.
5. Create a list of permutations - Excel 365
A manufacturing company has four different types of metals available (A, B, C, and D), repetition is allowed. They want to explore various permutations by arranging two metals together. The goal is to determine all the possible arrangements or permutations that can be formed by selecting two metals at a time from the four available options.
To calculate the number of permutations and list them out, we can use the following approach:
Total number of metals = 4
Number of metals to be selected for each arrangement = 2
We can use the PERMUT function to calculate the number of permutations:
=PERMUTATONA(n,r)
Where:
n = the total number of items (in this case, metals)
r = the number of items being selected or arranged
Substituting the values:
n = 4 (four different metals)
r = 2 (selecting two metals for each arrangement)
The result is 16 different permutations.
The Excel 365 dynamic formula in cell I3 creates a list of permutations with repetition, I demonstrate a formula that creates permutations without repetition below.
Formula in cell I3:
This formula is dynamic meaning it spills to more or fewer cells automatically if you change the numbers in cells F5 and F6.
Explaining formula
Step 1 - Calculate items in list
The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a larger group of elements. Repetition is allowed.
PERMUTATIONA(number, number-chosen)
PERMUTATIONA(F5, F6)
becomes
PERMUTATIONA(4, 2)
and returns 16.
Step 2 - Create a list of sequential numbers
The SEQUENCE function creates a list of sequential numbers to a cell range or array.
SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(PERMUTATIONA(F5,F6))-1
becomes
SEQUENCE(16)-1
becomes
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-1
and returns
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}
Step 3 - Number raised to a power
^ character lets you raise a number to a power of a given number. It is the same as the POWER function but shorter.
F5^(SEQUENCE(,F6,F6-1,-1)+1)
becomes
F5^(SEQUENCE(,2,1,-1)+1)
becomes
F5^({1, 0}+1)
becomes
4^{2, 1}
and returns {16, 4}.
Step 4 - Calculate remainder
The MOD function returns the remainder after a number is divided by a divisor.
MOD(number, divisor)
MOD(SEQUENCE(PERMUTATIONA(F5,F6))-1,F5^(SEQUENCE(,F6,F6-1,-1)+1))
becomes
MOD({0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15},{16, 4})
and returns
{0, 0; 1, 1; 2, 2; 3, 3; 4, 0; 5, 1; 6, 2; 7, 3; 8, 0; 9, 1; 10, 2; 11, 3; 12, 0; 13, 1; 14, 2; 15, 3}.
Step 5 - Round numbers down
Thr FLOOR function rounds a number down, toward zero, to the nearest multiple of significance.
FLOOR(number, significance)
FLOOR(MOD(SEQUENCE(PERMUTATIONA(F5,F6))-1,F5^(SEQUENCE(,F6,F6-1,-1)+1))/(F5^SEQUENCE(,F6,F6-1,-1)),1)+1
becomes
FLOOR({0, 0; 1, 1; 2, 2; 3, 3; 4, 0; 5, 1; 6, 2; 7, 3; 8, 0; 9, 1; 10, 2; 11, 3; 12, 0; 13, 1; 14, 2; 15, 3}/({4,1}),1)+1
becomes
FLOOR({0, 0; 0.25, 1; 0.5, 2; 0.75, 3; 1, 0; 1.25, 1; 1.5, 2; 1.75, 3; 2, 0; 2.25, 1; 2.5, 2; 2.75, 3; 3, 0; 3.25, 1; 3.5, 2; 3.75, 3},1)+1
becomes
{0, 0; 0, 1; 0, 2; 0, 3; 1, 0; 1, 1; 1, 2; 1, 3; 2, 0; 2, 1; 2, 2; 2, 3; 3, 0; 3, 1; 3, 2; 3, 3}+1
and returns
{1, 1; 1, 2; 1, 3; 1, 4; 2, 1; 2, 2; 2, 3; 2, 4; 3, 1; 3, 2; 3, 3; 3, 4; 4, 1; 4, 2; 4, 3; 4, 4}.
Step 6 - Get values
The INDEX function gets a value based on a row number and column number (optional).
INDEX(array, [row_num], [column_num], [area_num])
INDEX(B3:E3,FLOOR(MOD(SEQUENCE(PERMUTATIONA(F5,F6))-1,F5^(SEQUENCE(,F6,F6-1,-1)+1))/(F5^SEQUENCE(,F6,F6-1,-1)),1)+1)
becomes
INDEX(B3:E3, {1, 1; 1, 2; 1, 3; 1, 4; 2, 1; 2, 2; 2, 3; 2, 4; 3, 1; 3, 2; 3, 3; 3, 4; 4, 1; 4, 2; 4, 3; 4, 4})
becomes
INDEX({"A","B","C","D"}, {1, 1; 1, 2; 1, 3; 1, 4; 2, 1; 2, 2; 2, 3; 2, 4; 3, 1; 3, 2; 3, 3; 3, 4; 4, 1; 4, 2; 4, 3; 4, 4})
and returns
{"A", "A"; "A", "B"; "A", "C"; "A", "D"; "B", "A"; "B", "B"; "B", "C"; "B", "D"; "C", "A"; "C", "B"; "C", "C"; "C", "D"; "D", "A"; "D", "B"; "D", "C"; "D", "D"}.
Step 7 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
INDEX(B3:E3, FLOOR(MOD(SEQUENCE(PERMUTATIONA(F5,F6))-1,F5^(SEQUENCE(,F6,F6-1,-1)+1))/(F5^SEQUENCE(,F6,F6-1,-1)),1)+1)
becomes
LET(y, F6, x, SEQUENCE(,y,y-1,-1), INDEX(B3:E3,FLOOR(MOD(SEQUENCE(PERMUTATIONA(F5,y))-1,F5^(x+1))/(F5^x),1)+1))
Recommended articles
List permutations with repetition [UDF]
Create permutations [UDF]
Permutations with and without repetition
List all permutations with a condition
6. PERMUTATIONA function not working
The PERMUTATIONA function returns:
- #NUM! error value if numeric arguments are values that are not valid.
- #VALUE! error value if numeric arguments use data types that are nonnumeric.
'PERMUTATIONA' function examples
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
Functions in 'Statistical' category
The PERMUTATIONA function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form