Calculating Permutations and Combinations If you've ever taken a sta s cs course, you're familiar with the concept of permuta ons and combina ons. Permuta ons and combina ons are used to determine the number of possible ways to select or arrange a group of objects. Two factors come into play: Whether the order of the selec on ma ers, and Whether repe ons are allowed FuncƟons for PermutaƟons and CombinaƟons Four Excel func ons are relevant, and the table below summarizes which func on to use for each situa on.
RepeƟƟons Are Allowed RepeƟƟons Are Not Allowed
Order MaƩers PERMUTATIONA
Order Doesn't MaƩer COMBINA
PERMUT
COMBIN
Note: The PERMUTATIONA and COMBINA func ons are new to Excel 2013, and are not compa ble with previous versions.
Example 1 Using the digits 0‐9, how many different 4‐digit PINs can be generated? With a PIN, numbers can be repeated, and the order of the numbers is important. This formula returns 10,000: =PERMUTATIONA(10,4)
978‐1‐118‐49044‐0 Wiley and the Wiley logo are registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.
Calculating Permutations and Combinations Example 2 An ice cream shop has 24 flavors of ice cream. How many 2‐scoop combina ons can be made? Here, we assume that the order of the scoops is not important, and flavors can be repeated. For example, two scoops of vanilla counts as a combina on. This formula returns 300: =COMBINA(24,2)
Example 3 Ten people enter a contest, and 1st, 2nd, and 3rd place winners will be chosen. How many different winner configura ons are possible? In this example, the order certainly ma ers, and repe ons are not allowed, because a single person can only win one award. This formula returns 720: =PERMUT(10,3)
Example 4 At a baseball tryout, 16 kids show up. How many different 9‐person teams can be made? In this case, the order doesn't ma er, and repe ons are not allowed (because a single person can't be on a team more than once). This formula returns 11,440: =COMBIN(16,9)
About the Author John Walkenbach, arguably the foremost authority on Excel, has wri en 50+ books, including mul ple edi ons of the bestselling Excel Bible and Excel Power Programming with VBA, as well as more than 300 ar cles for publica ons such as PC World, InfoWorld, and Windows. He is the creator of the award‐winning Power U lity Pak and provides Excel informa on and insight on his Spreadsheet Page website.
978‐1‐118‐49044‐0 Wiley and the Wiley logo are registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.