The COMBINA function is used to return the total number of possible combinations for a given set numbers of items with repetitions. In short, giving the total possible number of possible groups for a given number of items, assuming the same item can be picked multiple times.
Syntax
= COMBINA([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 picking out flavors for an ice cream cone. If you have 3 flavors to choose from, and have a cone that will fit 2 scoops, you could use the formula:
= COMBINA(3,2) = 6
This formula will return 6, which is the total possible number of combinations that 3 flavors could be split into groups (scoops) of 2. If we lettered them: V, C, and S for vanilla, chocolate, and strawberry, the possible combinations would be:
What sets COMBINA apart from the COMBIN function is that COMBINA, does count any combinations with repetition, such as VV, CC, and SS. If you are looking for repetition to be excluded, the COMBIN function is the one you'll want to use instead.
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 PERMUTATIONA function instead.
Here is a table to help you figure out which function you need to use:
Note:
- The COMBINA 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 Combinations With Repetition
This is the standard use case for the COMBINA function. With a specific set size and a specific group size, we can calculate all of the different ways in which the items in each set could be combined (with repetition).
In the first example, out of 4 items, there are 10 different ways in which they could be paired into groups of two. For the last example, out of 40 items, there are 123,410 different ways in which they could be split up into groups of 4.
= COMBINA(B3,C3)2. Calculate Possible Product Sale Combinations
Lets say you run a t-shirt business and would like to know how many different combinations of your products could be sold during an average day.
First we need some assumptions. Lets assume that:
- Each color t-shirt has an equal chance of being purchased
- You average 15 customers a day
- You only have 5 color options available, red, blue, green, orange, white, and no limit on availability.
Because a color can be purchased multiple times, we want to use the COMBINA function rather than the COMBIN function.
= COMBINA(15,5)
By using this function, we see that there are 11,628 possible combinations in which 15 people could pick from your 5 shirt colors.
3. Calculate Possible Scoops of Ice Cream Combinations
Similar to the example above, lets look at that ice cream scoop example again with a couple more variations.
We can see here that if the grouping size (cone size) is 1, then the number of possible combinations will match our number of input values. But, as we increase cone size or the number of flavors to choose from, the possible combinations grow exponentially.
= COMBINA(B3,C3)