Combinations and permutations are fundamental deal with the arrangement and selection of objects or elements from a set. While both combinations and permutations involve counting possibilities, they differ in terms of the order and repetition of elements. In this post, I’ll show you how to calculate both permutations and combinations in Excel, both with and without replacement and repetition.
What is a permutation?
A permutation refers to an arrangement of objects from a set, where the order matters. In other words, permutations consider the different ways in which objects can be arranged. For example, let’s take the set {A, B, C}. The permutations of this set would be ABC, ACB, BAC, BCA, CAB, and CBA. The number of permutations can be calculated using the formula:
nPr = n! / (n – r)!
Where n represents the total number of objects in the set and r denotes the number of objects being selected for each arrangement. The exclamation mark denotes factorial, which means multiplying a number by all the positive integers less than it down to 1.
Suppose you want to consider all 26 letters of the alphabet — this would be the n value. Now, if you want to know all the different permutations when selecting 3 characters, that would be your r value. To calculate the number of different permutations, your formula would be as follows:
nPr = 26! / (26 – 3)!
This gets simplified to:
nPr = 26! / 23!
Rather than doing this complex calculation, you can first cancel out the numbers going up until 23 on each side. Then you’re left with the following:
26*25*24 = 15,600
This tells us that there are 15,600 different permutations when selecting the three letters from the alphabet. The preceding formula assumes that you are not replacing objects. If, however, you are replacing them then that means you can select the same item multiple times. In this situation, the formula for calculation permutations with replacement is as follows:
=n^r
When choosing 3 letters from the alphabet, the result would be:
26^3 = 17,576
In this situation, you have will have more possible permutations since the objects can repeat.
What is a combination?
If you’re talking about combinations, then the difference here is that you don’t consider the order as you would with permutations. In the earlier example where the set was {A,B,C} and you needed to select three characters, there would only be 1 combination. That’s because whether it’s ABC, BAC, CAB, or any other order of the characters, that’s irrelevant since combinations don’t care about order. The letters are all the same, and thus, there would only be 1 possible combination.
The number of combinations can be determined using the formula:
nCr = n! / (r!(n – r)!)
Let’s do this again, when selecting 3 letters from the alphabet:
nCr = 26! / (23! (26-23)!)
That formula simplifies to this:
nCr = 26! / 23! (3)!
And again, up until 23, everything gets canceled out, leaving the following:
nCr = (26*25*24)/(3*2*1)
This is the same as the result from the permutation calculation but the difference is that the denominator is now larger; it is calculated as the r factorial. Upon completing this formula, the result is:
15,600/6 = 2,600
This is the number of permutations divided by the factorial of 3, the number of selections. If there are replacements and you select the same item multiple times, this is the formula:
(n + r -1)! / (r!(n-1)!)
This results in the following calculation:
(26 + 3 -1)! / (3!(26-1)!)
This simplifies to:
(28)!/(3!(25)!)
Which further simplifies to:
(28*27*26)/(3*2*1) = 3,276
How to calculate permutations and combinations in Excel
In the above examples, I showed you how you can calculate permutations and calculations manually. But with Excel, there are formulas that can do the work for you.
To calculate permutations, we use the PERMUT function when there are no replacements. In the first example where there were 3 items chosen from a set of 26, this is the PERMUT formula:
=PERMUT(26,3) => 15,600
It returns the same result. If there are replacements, then the PERMUTATIONA formula is used:
=PERMUTATIONA(26,3) => 17,576
For combinations, the default function is COMBIN:
=COMBIN(26,3) => 2,600
And when there are replacements, COMBINA is used:
=COMBINA(26,3) => 3,276
These results all match up with the calculations from the previous examples, where they were done manually.
How to calculate the Powerball odds
For the Powerball, there are two draws that happen. The first is where 5 numbers are chosen from 69 possible items. Then, in the second draw, there is 1 number that selected from 26 possibilities. When dealing with probabilities, when we want both events to happen, we need to multiply the odds. The first step will be to calculate the individual combinations. Since there is no replacement, the formula for the first draw is as follows:
=COMBIN(69,5) => 11,238,513
For the second draw, the calculations straightforward since there is only 1 item selected from 26 possible options, so there can only be 26 combinations. Thus, to calculate the Powerball odds where you win both the first draw and the second draw, you need to multiple the odds of winning the first draw by the odds of winning the second draw:
=11,238,513 x 26 = 292,201,338
This tells us that the odds of winning both draws are 1 in 292 million.
If you liked this post on How to Calculate Combinations and Permutations in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.