# 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 signsUse 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 OscarYou can contact me through this contact form