# How to use the PERMUT function

The PERMUT function returns the number of permutations for a set of elements that can be selected from a larger number of elements. The internal order of the elements is important in permutations.

#### Table of Contents

## 1. PERMUT Function Syntax

PERMUT(*number*, *number_chosen*)

## 2. PERMUT Function Arguments

number |
Required. A whole number larger than 0 (zero) that represents the total number of elements. |

number_chosen |
Required. A whole number larger than 0 (zero) that represents the number of elements in each permutation. |

## 3. PERMUT Function Example

Formula in cell F3:

Column B, C and D demonstrate how many permutations there are when 2 elements are selected out of 3 elements [A, B, C].

The six permutations are [A,B] ,[A,C] ,[B,C] ,Â [B,A] ,Â [C,B] and [C,A].

## 4. Create permutations using a formula

The Excel 365 dynamic formula in cell I3 creates a list of permutations with repetition, I have not yet figured out how to create a list of permutations without repetition.

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

## 5. Find the optimal permutation - Excel Solver

The Solver is a built-in feature that you can use in Excel to quickly find an optimal permutation.

A machine has five different tools named A, B, C, D, and E. The table in cell range B2:G7 shows how much time is needed to change one tool to another tool. For example, changing tool A to tool B takes 5 minutes.

In what order do we need to change tools to find the least amount of time needed? The Solver lets you change the order in cell range I3:I7 automatically, the worksheet calculates the time in cells K3:K7 based on the table.

Cell K3 contains a sum function:

The Solver uses the value in K3 to find the optimal solution by changing numbers in I3:I7. The formula in J3:J7 returns the Tool name.

Formula in cell J3:

The INDEX function gets the tool name in C2:G2 based on the number in cell I3. The number in cell I3 changes when the Solver tries different permutations.

Formula in cell K4:

Copy this formula to cells below as far as needed.

### 5.1 Explaining formula

Cell K3 contains 0 (zero).Â The formula in K4 uses the tool name in cell J4 and the tool name in the cell above J4 which is cell J3 to find the appropriate time value in the table.

#### Step 1 - Calculate row

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value, lookup_array, [match_type]*)

MATCH(J4, $B$3:$B$7, 0)

becomes

MATCH("B", {"A";"B";"C";"D";"E"}, 0)

and returns 2.

#### Step 2 - Calculate column

MATCH(J3, $C$2:$G$2, 0)

becomes

MATCH("A",Â {"A","B","C","D","E"}, 0)

and returns 1.

#### Step 6 - Get time value

The INDEX function gets a value based on a row number and column number (optional).

INDEX(*array*,Â *[row_num]*,Â *[column_num], [area_num]*)

INDEX($C$3:$G$7, MATCH(J4, $B$3:$B$7, 0), MATCH(J3, $C$2:$G$2, 0))

becomes

INDEX($C$3:$G$7, 2, 1)

becomes

INDEX(

{"-", 5, 3, 7, 4;

**5**, "-", 9, 11, 8;

3, 9, "-", 2, 5;

7, 11, 2, "-", 7;

4, 8, 5, 7, "-"}

, 2, 1)

and returns 5. 5 is the first value in the second row.

### 5.2 Setting up the Solver

- Go to tab "Data".
- Press with the left mouse button on the "Solver" button.
- Press with left mouse button on the arrow next to "Set Objective" and select cell K9. This value is the sum of all times K3:K7.
- Press with the left mouse button on the radio button named "Min" to select it. This lets the solver know that we are looking for a permutation that returns the smallest number.
- Press with left mouse button on the arrow next to "By changing variable cells" and select cells I3:I7. These cells are populated with different permutations.
- Press with the left mouse button on the "Add" button. A dialog box appears, this lets you apply constraints.

- Press with mouse on the arrow next to "Cell Reference:", then select cell range I3:I7.
- Press with mouse on the equal sign, select "dif", this lets the Solver know that all numbers in cell range I3:I7 must be different.

- Press with left mouse button on the "OK" button to return to the previous dialog box.
- Change solving method to "Evolutionary".
- Press with mouse on the check box "Make unconstrained variables Non-negative to enable it.

- Press with left mouse button on the "Solve" button to start.

- A dialog box appears, press with left mouse button on the "OK" button.

The solver found permutations iteration 4, 3, 5, 1, and 2 to be the optimal solution. In other words, this permutation returns the lowest wait time for a given set of tools.

## 6. List permutations with repetition - UDF

This blog post demonstrates a custom function (UDF) that createsÂ permutations. Repetition is allowed. The custom function lets you specify the number of items to use and it will return an array of numbers.

Array formula:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### 6.1 VBA Code

Function ListPermut(num As Integer) 'Permutations with repetition Dim c As Long, r As Long, p As Long Dim rng() As Long p = num ^ num ReDim rng(1 To p, 1 To num) For c = 1 To num rng(1, c) = 1 Next c For r = 2 To p For c = num To 1 Step -1 If c = num Then rng(r, c) = rng(r - 1, c) + 1 ElseIf rng(r, c) = 0 Then rng(r, c) = rng(r - 1, c) End If If rng(r, c) = num + 1 Then rng(r, c) = 1 rng(r, c - 1) = rng(r - 1, c - 1) + 1 End If Next c Next r ListPermut = rng End Function

### 6.2 How to add the user defined function to your workbook

The image above shows a macro that is not used in this article, it is only there to show you where to paste the code.

- Press Alt-F11 to open visual basic editor.
- Press with left mouse button on Module on the Insert menu.
- Copy the above user defined function.
- Paste it to the code module.
- Exit visual basic editor.
- Select sheet1.
- Select cell range A1:C27.
- Type
**=ListPermut(3)**in formula bar and press CTRL+SHIFT+ENTER (Array formula).

If you don't know how to enter an array formula then read the detailed instructions below the image.

### 6.3 Example

Array formula in A3:C29:

### 'PERMUT' function examples

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]

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 PERMUT function function is one of 74 functions in the 'Statistical' category.

### Excel function categories

### Excel categories

### 5 Responses to “How to use the PERMUT function”

### Leave a Reply

### 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

I would need to use the ListPermut function with argument 7. As I can understand it only works to a maximum of 6.

Thanks,

Antonio

I'll post an answer as soon as I can.

I'm trying to use the function for obtaining the possibilities of distributing 3 things over 5 places (repetition allowed), but the function is not workings on this option, can you please help ??

example:

A,A,B,B,C

A,A,B,B,B

A,C,B,B,A

and so on....

Thanks in advance

and also the number 5 can be changed to be from 1 to 8 but the number 3 is constant. Thank you

I have found the equation in section 4 very helpful for what I am using it for, thank you for this!

I notice that the equation only limits you to a chosen number of 4, if I wanted for example 5, then this would break the formula, but I found that is because there are more rows in the array than there are in the whole excel worksheet. For example, for PERMUTATIONA(21,5) there are 4,084,101 permutations and excel only has 1,048,576 rows.

However, I do not want all 4,084,101 permutations, but rather only the ones that sum up to a given value.

For example, I have the variables {0, 5, 10, 15, 20, 25,..., 100} that I want to find all the possible permutations with repetition where any 5 numbers sum up to 35. This should dramatically reduce the number of rows in the array to 330 possible permutations.

Is there a way to include this in the already built formula? I feel like there is but I'm struggling to figure it out.