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

### 'PERMUT' function examples

The following 7 articles contain the PERMUT function.

Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]

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

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]

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

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

## Functions in this article

### Functions in 'Statistical' category

The PERMUT function function is one of many 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