COMBIN Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the COMBIN Function in Excel to calculate the number of ways a given number of items can be ordered.

COMBIN Function Overview

The COMBIN function calculates the number of combinations for a given number of items. Let’s look at an example:

Let’s say you live in New Zealand and need a get-rich scheme. You figure the New Zealand lottery is a good bet, so buy some tickets. After playing for a few years your return on investment is not as good as you thought it would be. Why not?

In the New Zealand lottery there are total of 40 numbers to choose from and you need to choose 6. We can find out how many combinations there are using Excel’s COMBIN formula:

=COMBIN(C3,D3)

There are almost 4 million ways to choose six numbers from a list of 40 available! The odds of winning are 1 in 3,838,380. As a percentage, that’s 0.000026%!

In an average year, the jackpot can be expected to be won just 13 times.

Let’s take a look at Australia’s national lottery. There are 45 numbers available and players must choose 7. Those numbers aren’t much different from New Zealand’s.

That’s a big change in number of combinations from only a small change in the input numbers! The odds of winning in Oz Lotto is 1 in 45,379,620 – more than 10 times smaller than in New Zealand!

From our research, we only found 13 winners in the last ten years, although that information is very difficult to find so let us know if we’ve got this number wrong!

See if you can find out how many possible winning combinations there are for your state or country’s lottery!

Common Errors

#NUM! Combinations work in the natural world with natural numbers (including zero), so the argument of the COMBIN function is negative, a #NUM! error will be returned.

#NUM! In order to choose a number of items from a list of items, that number of chosen items must be equal to or less than the number of items.

Additional Notes

If you enter either the number or number_chosen as a non-integer, the decimal portion of the numbers will be ignored. Only integer values will be calculated.

The COMBIN formula does not take into account repetition. So while you can use COMBIN to calculate the number of ways to choose your dodgeball team, it cannot be used to calculate the number of ways a red M&M can be taken from a packet of red M&Ms.

From combinatorics, the COMBIN formula is equivalent to n choose r, where n is the number and r is the number_chosen:

COMBIN in Google Sheets

The COMBIN Function works exactly the same in Google Sheets as in Excel:

COMBIN Examples in VBA

To find the number of combinations a given number of items without repetition, use:

Dim Combination As Double

Combination = WorksheetFunction.Combin(Arg1, Arg2)

To find the number of combinations a given number of items with repetition, use:

Dim Combination as Double

Combination = WorksheetFunction.Combina(Arg1, Arg2)