The PERUMUTATIONA function is used to return the total number of possible permutations for a given set numbers of items with repetitions and where the order matters. In short, giving the possible number of ordered groupings for a given number of items, assuming the same item can be picked multiple times.
Syntax
= PERMUTATIONA([number],[number_chosen])number = the number of items within a set
number_chosen = the number of items in each group
Explanation
This function is part of the "Statistical" function group within Excel.
A permutation is a combination of numbers where the order matters. Think of this like trying to guess a correct password. The numbers and letters can be picked multiple times, and the order in which they are input or picked, do matter.
If you have a 5 character long numerical password (made up of the numbers 1-9 only), you could use the formula:
= PERMUTATIONA(9,5) = 59,049
This formula will return 59,049, which is the total possible number of permutations that you could create with 9 possible numbers, 5 numbers long, where the same number can be picked multiple times.
What sets PERMUTATIONA apart from the PERMUT function is that PERMUTATIONA, does count any permutations with repetition. A password of 11223 would be allowed. If you are looking for repetition to be excluded, the PERMUT function is the one you'll want to use instead.
Here is a table to help you figure out which function you need to use:
The formula used to calculate this number is:
Where n = number and r = number_chosen
Note:
- The PERMUTATIONA function will return a #VALUE if any of the inputs are non-numerical.
- A #NUM error will be returned if either number is less than zero, as you can't have negative groupings
- A #NUM error will also be returned if the number < number_chosen, as the grouping must be smaller than the total set size.
- Any decimal number will be truncated
Examples
1. How to Calculate Number of Possible Permutations With Repetition
This is the standard use case for the PERMUTATIONA function. With a specific number of items and a specific group size, we can calculate all of the different ways in which the items in each set could be combined where order matters, with repetition.
= PERMUTATIONA(B3,C3)
2. Calculate Possible Locker Padlock Permutation
Let's take a classic Master Lock padlock for example, and try to figure out how many possible codes you could create.
The standard combination lock (which are actually misnamed, they should be permutation locks), have the numbers 1-40 on them, or 0-39. They also require an input of 3 different numbers as their password length. By using the PERMUTATIONA function, we can calculate that the number of possible permutations in this configuration is 64,000.
= PERMUTATIONA(40,3) = 64,000
3. Calculate the Number of Possible Password Permutations for Any Website
Similar to the example above, let's look at a few more variations and the number of possible permutations that can be created from them.
We can see here that if we set the password length to 5, and then change the allowed input characters, the PERMUTATIONA function will return the number of possible permutations.
As we increase the number of characters allowed (or the password length) , the possible combinations grow exponentially.
= PERMUTATIONA(9,5)