Shahriar Abrar Rafid
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.
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.
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.
- 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.
Instantly, it’ll copy the formula to the lower cells and give us output in the remaining cells also.
Read More: How to Use PERMUT Function in Excel (with an Alternative)
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.
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.
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.
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.
