The COMBIN function is used to return the total number of possible combinations for a given set numbers of items without repetitions. In short, giving the total possible number of possible groups for a given number of items.
Syntax
= COMBIN([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 "Math and Trigonometry" function group within Excel.
It may help to think of this formula in terms of grouping people in teams. If you have 9 total people, and you need them in groups of three, you could use:
= COMBIN(4,2) = 6This formula will return 6, which is the total possible number of combinations that 4 people could be split into teams of 2. If we lettered them: A, B, C, D, the possible combinations would be :
AB, AC, AD, BC, BD, DC
COMBIN does not count any combinations with repetition, which means any combination of repeating items such as, AA, BB, ... DD. If you are looking for repetition to be accounted for, use the similar COMBINA formula. COMBINA is identical except for this change.
The formula used to calculate this number is:
Where n = number and r = number_chosen
A combination is any set/subset or group of items within a number of values. The internal order of those items does not matter and combinations are distinct from permutations. If order does matter, you'll want to use the PERMUT function instead.
Here is a table to help you figure out which function you need to use:
Note:
- The COMBIN 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 Find the Number of Possible Combinations in a Given Set
This is the most basic use case for COMBIN. In column B, we have the number of values within the set, and in column C, we have the size of the groupings we want to give.
So, for the first example, out of 4 items, how many ways can we group them into pairs of 2? The answer would be 6. Using the example above again, if we lettered the 4 items as: A, B, C, D, the possible combinations would be : AB, AC, AD, BC, BD, DC
= COMBIN(B3,C3)
2. How to Find the Number of Possible Team Configurations
Like the previous example, COMBIN can be used to give us the number of ways that a group of players could be split into teams. As the number of players increases, the number of possible configurations increases dramatically.
If we wanted to split up 40 people into two equal teams of two, the number of possible configurations we could possibly draft, is into the hundred billions, at 137,846,528,820 possible combinations.
= COMBIN(B3,C3)3. How to Find the Odds of Winning the Lottery
Because most lotteries operate by picking a specific amount of balls from a set pool of numbers, the COMBIN function can calculate the odds of us winning the lottery for our set of numbers.
Let's take the Powerball for example. In the Powerball 5 balls are pulled from a pool of 69 total numbers 1 -> 69.
= COMBIN(69,5)
Using this formula, we see that there are a total of 11,238,513 possible combinations that could be pulled from that set of 69 balls. But, there is an additional catch. To get the top level price, your 5 numbers have to be pulled and the Powerball must also be pulled.
Every time balls are pulled from the main group, a second ball is picked from a secondary group of 26 balls. Out of 26 balls, one is the red "Powerball" that upgrades the winnings to the first place, ludicrous amounts of money, prize. However, pulling the correct Powerball out of the 26, is odds of 1/26, or 3.85%.
To get your chances of winning the ultimate first prize, we have to multiply the 11,238,513 by 26. So, your odds of winning the top prize in the Powerball is 1 in 292,201,338 or 0.000000342%.
Full math equation here:
This can be calculated in Excel using this formula which replicates the above equation:
= 1 / (((FACT(69)) / (FACT(5) * FACT(69-5))) * 26)Or can be shortened using the COMBIN function to:
= 1 / ((COMBIN(69,5) * 26))