Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Permutations Without Repetition in Excel

Permutation is such a great mathematical concept that has a significant impact on every aspect of education. We can see its usage in math, quantum physics, computer science, etc. In this article, we’ll demonstrate how we can calculate permutations in 3 different methods without repetition in Excel. So, let’s go through the article entirely to grab a good dexterity on it.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Basics of Permutation

The permutation is a computational method that establishes the total number of alternative configurations in a collection when the sequence of the arrangements matters. Picking only a few items from a collection of options in a specific sequence is a common task in arithmetic problems. Permutations are frequently confused with another mathematical concept, combination. The sequence in which the components are selected does not, however, affect combinations. In other words, whereas “ab” and “ba” are equal in combinations, they are deemed to be different arrangements in permutations. Let’s see an example to understand it better.

Let’s say you have three letters: A, B, and C. Suppose we tell you to make different words containing two letters with these letters. So, you could construct AB, BA, BC, CB, AC, and CA. Hence, there would be 6 different 2-letter words created from these 3 letters. Therefore, here, 3 is the total number of objects in the set. We can express it with the letter n. And 2 is the specific number of chosen objects that can be substituted for k. So, the general formula for permutation is the following.

nPk = n! / (n-k)!

3 Methods to Calculate Permutations Without Repetition in Excel

For ease of understanding, we are going to use a List of Numbers and Groups. This dataset contains the Numbers and Chosen Group under columns B and C respectively.

excel permutations without repetition

Now, we’ll use this dataset to calculate permutations using 3 various methods without repetition in Excel. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Using PERMUT Function

In our first method, we’ll get help from the PERMUT function. It returns the number of permutations without repetition for a certain number of items that can be chosen from a number of objects. So, let’s see the steps below to do it our way.

📌 Steps:

  • At the very beginning, create a new column with the heading called Permutations under Column D.

Using PERMUT Function to do permutations without repetitions in Excel

  • Then, select cell D5 and enter the following formula into that cell.
=PERMUT(B5,C5)

Here, B5 and C5 represent the first Number and the first Chosen Group correspondingly.

  • After that, press the ENTER key.

  • At this time, bring the cursor to the right-bottom corner of cell D5 and it will look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Following this, double-click on it.

usign fill handle to do permutation without repetitions in excel

Instantly, it’ll copy the formula to the lower cells and give us output in the remaining cells also.

Using PERMUT Function to do permutations without repetitions


2. Utilizing FACT Function

In this section, we’ll follow the manual process to calculate permutations in Excel. To do this, we’ll insert the FACT function. It’s simple and easy; just follow along.

📌 Steps:

  • First, go to cell D5 and insert the formula below.
=FACT(B5)/FACT(B5-C5)
  • Then, press ENTER.

Utilizing FACT Function

That’s how we can easily find the permutations for defined numbers and groups.


3. Applying Combined Formula

In our last method, we’ll employ multiple functions to achieve our goals. So, without further delay, let’s dive in!

📌 Steps:

  • Firstly, select cell C5 and paste the following formula.
=SUM(IF(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),CHAR(ROW(INDIRECT("1:255"))),"")),1,0))

Formula Breakdown

  • ROW(INDIRECT(“1:255”)) → Here the ROW and INDIRECT functions return the numbers 1 to 255 in 255 different cells in a vertical array.
  • CHAR(ROW(INDIRECT(“1:255”))) → the CHAR function returns a character based on the number given as an argument. Like, for 33, it returns an Exclamation mark (!) called Factorial.
  • UPPER(B5) → the UPPER function converts the text to uppercase letters.
    • Output → AABCF.
  • SUBSTITUTE(UPPER(B5),CHAR(ROW(INDIRECT(“1:255″))),””) → the SUBSTITUTE function gets the output of UPPER(B5) as the text argument. Then, it takes the output of CHAR(ROW(INDIRECT(“1:255”)) as the old_text argument and a blank space as the new_text argument.
  • LEN(SUBSTITUTE(UPPER(B5),CHAR(ROW(INDIRECT(“1:255″))),””)) → the LEN function returns the length of the text strings in the vertical array.
  • IF(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),CHAR(ROW(INDIRECT(“1:255″))),””)),1,0) → the IF function implies a logical test. If the result of the subtraction becomes greater than zero, then it returns 1, otherwise, it’ll return 0.
  • SUM(IF(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),CHAR(ROW(INDIRECT(“1:255″))),””)),1,0)) → the SUM function totals all the output in the array and returns the output in cell C5.
    • Output → 4
  • Then, tap the ENTER key.

Applying the Combination of Multiple Functions

Now, we have extracted the number of unique letters in each text string. Therefore, we can easily calculate the number of permutations for each row. For example, we took the group of 2 letters for the first example.

  • So, go to cell E5 and write down the following formula.
=PERMUT(C5,D5)
  • As always, press ENTER.

Applying the Combination of Multiple Functions to do permutations without repetition in Excel

You can use this formula for text strings of various lengths as you wish.


Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself.


Conclusion

This article explains how to calculate permutations without repetitions in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo