A to Z of Excel Functions: The COMBINA Function
4 September 2017
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the COMBINA function.
The COMBINA function
This function returns the number of combinations (with repetitions) for a given number of items. If this sounds a little confusing, think of it this way: you have a number of balls in the bag, each with a different number on it. You take one ball out at random, record its number, then replace it. You do this number_chosen times. Ignoring the sequence of the numbers selected, COMBINA deduces the number of different combinations. Since there is replacement, number_chosen may exceed the number too.
For example the selecting three numbers from a bag of balls numbered 1 to 4 would have the following 20 combinations:
111, 112, 113, 114, 122, 123, 124, 133, 134, 144, 222, 223, 224, 233, 234, 244, 333, 334, 344 and 444.
The COMBINA function employs the following syntax to operate:
The COMBINA function has the following arguments:
- number: this is required and must be greater than or equal to zero. Microsoft states that number must be greater than or equal to number_chosen but this requirement does not seem to hold in practice (see my examples below)
- number_chosen: this is also required and Must be greater than or equal to zero too
- non-integer values for both arguments will be truncated.
It should be further noted that:
- If the value of either argument is outside of its constraints, COMBINA returns the #NUM! error value
- If either argument is a non-numeric value, COMBINA returns the #VALUE! error value
- The following equation is used:
- In the equation above, N is number and M is number_chosen.
Please see my examples below:
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.
A full page of the function articles can be found here.