Use of PERMUTATIONA Function in Excel (4 Simple Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will explore how to use the PERMUTATIONA function in Excel to generate phone numbers, pin codes, license plate numbers, and even Powerball lottery numbers.

Overview of Excel PERMUTATIONA Function

We will use the PERMUTATIONA function to generate phone numbers by choosing a set of 10 numbers between 0 and 9 and arranging them in different orders. The second example will focus on generating PIN codes to have secure access to devices or systems. We will choose a set of 4 numbers between 1 and 6 and arrange them in different orders to create a list of unique pin codes.

In the third example, we will use the PERMUTATIONA function to generate license plate numbers, which are used to identify vehicles on the road. We will choose a set of letters and numbers and arrange them in different orders to create a list of unique license plate numbers. Finally, we will explore how you can use the PERMUTATIONA function to generate Powerball lottery numbers by choosing a set of numbers and arranging them in different orders.


Basics of PERMUTATIONA Function

The PERMUTATIONA function in Excel calculates the total number of possible permutations with repetition for a given set of objects. It is particularly useful for generating unique sets of numbers, such as phone numbers, pin codes, and lottery numbers.

The syntax for the PERMUTATIONA function is as follows:

Syntax of PERMUTATIONA Function

=PERMUTATIONA(number, number_chosen)

The “number” argument represents the total number of objects available, while the “number_chosen” argument represents the number of objects selected for each permutation. Both arguments must be integers greater than or equal to 0.

The function returns the total number of possible permutations with repetition, represented as a numerical value.

For example, the formula =PERMUTATIONA(5, 3) would calculate the total number of possible 3-number permutations that can be generated from a set of 5 numbers, including repeated numbers.


What Is Permutation?

In mathematics, Permutation is a way of arranging a set of objects in a particular order. The order of the arrangement matters in permutation. Otherwise, If inside the arrangement, the order of the objects does not matter, the arrangement could be called “Combination”.

There are 2 types of permutations, with and without repetition.

Permutations Without Repetition:

The formula to calculate the number of permutations without repetition is:

n! / (n - r)!

Where n is the total number of objects in the set and r is the number of objects that you want to arrange. The exclamation mark (!) represents the factorial function.

Suppose, we have a set of 4 numbers: {1, 2, 3, 4}, and we want to arrange them in groups of 3 without repetition. The possible permutations without repetition are:

4! / (4 - 3)! = 4! / 1! = 24

So, there are 24 possible permutations of the set {1, 2, 3, 4} arranged in groups of 3 without repetition.

Permutations With Repetition:

The formula to calculate the number of permutations with repetition is:

n^r

Where n is the total number of objects in the set and r is the number of objects being arranged.

Suppose we have the same set of 4 numbers: {1, 2, 3, 4}, and we want to arrange them in groups of 3 with repetition allowed. The possible permutations with repetition are:

4^3 = 64

So, there are 64 possible permutations of the set {1, 2, 3, 4} arranged in groups of 3 with repetition allowed.


Difference Between PERMUT and PERMUTATIONA Functions in Excel

In Excel, the PERMUT and PERMUTATIONA functions are used to calculate the number of possible permutations of a set of objects.

The main difference between the two functions is how they handle the repetition of objects in the set.

The PERMUT function calculates the number of permutations without repetition, similar to the formula provided earlier. It takes two arguments: n and r, where n is the total number of objects in the set, and r is the number of objects being arranged. The syntax for the PERMUT function is:

=PERMUT(n, r)

For example, to calculate the number of permutations of the set {1, 2, 3, 4} arranged in groups of 3 without repetition, we can use the PERMUT function:

=PERMUT(4, 3)

This returns the value 24, which is the same as the number of possible permutations calculated using the formula.

Output Using PERMUT Function

On the other hand, the PERMUTATIONA function calculates the number of permutations with repetition, similar to the formula provided earlier. It takes two arguments: n and r, where n is the total number of objects in the set, and r is the number of objects being arranged. The syntax for the PERMUTATIONA function is:

=PERMUTATIONA(n, r)

For example, to calculate the number of permutations of the set {1, 2, 3, 4} arranged in groups of 3 with repetition allowed, we can use the PERMUTATIONA function:

=PERMUTATIONA(4, 3)

This returns the value 64, which is the same as the number of possible permutations calculated using the formula.

Output Using PERMUTATIONA Function

So, to summarize, the PERMUT function is used to calculate permutations without repetition, while the PERMUTATIONA function is used to calculate permutations with repetition in Excel.


Excel PERMUTATIONA Function: 4 Examples

Now we are demonstrating four practical uses of the PERMUTATIONA function of Excel in real life.


1. Generating Phone Numbers Using PERMUTATIONA Function

In this example, we are trying to calculate the total number of 11-digit cell phone numbers that can be generated, which contain different combinations of the numbers from 0 to 9.

First, in cell C5, type the following formula and press ENTER.

=COUNT(B5:B14)

Here, we used the function COUNT() to count the Usable Digits from Column B.

Using COUNT() Function to Count Available Number of Digits for Generating Phone Numbers

Then, in cell D5 type the required number of digits to generate a single cell phone number.

Later, in cell E5, type the following formula and press ENTER.

=PERMUTATIONA(C5,D5)

Here, the function PERMUTATIONA() dictates the total number of possible cell phone numbers.

Using PERMUTATIONA() Function to Calculate Possible Units of Phone Numbers

You can see some sample Cell Phone Numbers in Column F.


2. Generating PIN Codes Using PERMUTATIONA Function

In this example, we are trying to calculate the total number of 4-digit PIN codes. These codes will contain different combinations of the numbers from 1 to 6.

First, in cell C5 type the following formula and press ENTER.

=COUNT(B5:B14)

Here, the function COUNT() is used to count the Usable Numbers from Column B.

Using COUNT() Function to Count Available Number of Digits for Generating Pin Codes

Then, in cell D5 type the required number of digits to generate a single PIN code.

Later, in cell E5, type the following formula and press ENTER.

=PERMUTATIONA(C5,D5)

Here, we used the function PERMUTATIONA() to dictate the total number of possible PIN codes.

Using PERMUTATIONA() Function to Calculate Possible Units of Pin Codes

Some sample Pin Codes are shown in Column F.


3. Generating License Plate Numbers Using PERMUTATIONA Function

In this example, we are trying to calculate the total number of 8-digit license plate numbers. They will contain different combinations of the numbers from 0 to 9 and alphabets from A to J.

First, in cell D5 type the following formula and press ENTER.

=COUNTA(B5:C14)

Here, we used the function COUNT() to count the Usable characters from Columns B & C.

Using COUNT() Function to Count Available Number of Digits for Generating License Plate Numbers

Then, in cell E5 type the required number of digits to generate a single PIN code.

Later, in cell F5, type the following formula and press ENTER.

=PERMUTATIONA(D5,E5)

Here, the function PERMUTATIONA dictates the total number of possible license plate numbers.

Using PERMUTATIONA() Function to Calculate Possible Units of License Plate Numbers

You can see some sample License Plate Numbers in Column G.


4. Generating Powerball Lottery Numbers Using PERMUTATIONA Function

In this example, we are trying to calculate the total number of 6 numbered Powerball Lottery combinations. In the Powerball lottery, players choose five numbers from a pool of 69 and one additional number (the Powerball) from a pool of 26. The order of picking numbers doesn’t matter, but the same number can appear multiple times. Therefore, the lottery numbers are a permutation with repetition. For example, the combination “5-5-2-12-19-5” is a valid permutation with repetition in the Powerball lottery.

First, in cell D5 type the following formula and press ENTER.

=PERMUTATIONA(69,5)

Here, the function PERMUTATIONA dictates the total number of possible combinations to select 5 numbers from 69 numbers with repetition.

Using PERMUTATIONA() Function to Calculate Number of Possible Combinations for Choosing First 5 Numbers

Later, in cell E5, type the following formula and press ENTER.

=PERMUTATIONA(26,1)

Here, the function PERMUTATIONA() dictates the total number of possible outcomes to select the last number from 26 numbers.

Using PERMUTATIONA() Function to Calculate Number of Possible Combinations for Choosing The Last (6th) Number

Lastly, in cell F5, type the following formula and press ENTER.

=D5*E5

Here, the formula dictates the total number of possible outcomes to select the 6 numbers in total from two different sources.

Calculating Number of Possible Combinations for Choosing 6 Numbers in Total by Multiplication

You can see some sample Lottery Numbers in Column G.


Dealing with #VALUE! And #NUM! Errors While Working with PERMUTATIONA Function

In Excel, the #VALUE! error occurs when an argument in a function is of the wrong data type or when the function is expecting a value that isn’t present. With the PERMUTATIONA function, the #VALUE! error can occur if:

  • The n or r arguments are not valid numbers or are non-numeric values such as text, logical values, or empty cells.
  • The n or r arguments are negative or zero.

For example, if you use the PERMUTATIONA function with the arguments PERMUTATIONA(“10”, “5”), you will get a #VALUE! error since “10” is not a valid number.

On the other hand, the #NUM! error occurs when the result of a function is too large or too small to be displayed. With the PERMUTATIONA function, the #NUM! error can occur if:

  • The n or r arguments are greater than 255 or are non-numeric values.
  • The result of the permutation is too large, and Excel can not display it.

For example, if you use the PERMUTATIONA function with the arguments PERMUTATIONA(300, 10), you will get a #NUM! error since the result of the permutation is too large to be displayed in Excel.

To avoid these errors, ensure that the arguments you provide to the PERMUTATIONA function are valid numbers within the acceptable range.


Download Practice Book

Download the following practice workbook. It will help you understand the topic more clearly.


Conclusion

In conclusion, we have explored how to use the PERMUTATIONA Function in Excel to generate unique sets of numbers for various purposes. By using this function, we can simplify complex calculations and automate repetitive tasks, saving time and increasing productivity. We hope that this article has been informative and helpful in showcasing the capabilities of the PERMUTATIONA function in Excel. If you have any queries or comments, please feel free to leave them in the comment section below. Thank you for reading, and we hope you found this article useful.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo