Please note javascript is required for full website functionality.
MVP

Blog

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:

COMBINA(number, number_chosen)

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. 

Newsletter