A to Z of Excel Functions: The COMBINA Function

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.