COMBIN Function

What is the COMBIN Function?

The Combin Function returns the number of different possible combinations for a given number of items. The function is built into Microsoft Excel and falls under the Excel Math and Trigonometry functions.

When using this particular function, the order combinations do not matter. Nor does it allow for any repetitions. It was first added to Microsoft Excel in 2001 and helps determine groups, objects, product placements, and more.

It is meant as a worksheet function(WS) and is typically used as a calculation in the cell part of a sheet.

Individuals can use several functions in excel to give them the return they need. For example, here are two functions that provide similar returns to COMBIN():

1. COMBINA: Returns the number of combinations of two numbers, including the repetitions. 
2. PERMUT: Permut counts the number of Permutations between two numbers. A permutation is a combination where the order does matter.

A combination means that the order of a group does not matter.

How to use the COMBIN Function in Excel?

To use the Combin Function, it's essential to understand the purpose of the function.

The general purpose is to find how many combinations of a specified number you can get from another larger set number.

The Formula is represented by COMBIN(number, number_chosen); let's break this down into pieces to understand it better:

1. COMBIN() 

This is the function you will need to type into your cell on the sheet to begin the process of calculating your return.

2. Number 

The number part of the argument must be a positive integer. Additionally, it has to be greater than or equal to the (number_chosen) argument to logically and mathematically make sense. This serves as the number of items.

3. Number_chosen

The number_chosen part of the argument must be equal to or less than the number. This is the number of items that make up how many combinations are possible.

Each part of the argument is required for the function to compute correctly. If a non-integer value is imputed like a decimal, Excel will condense it into an integer. Combin Function will always return a numeric value.

COMBIN Function Examples

To help us understand the function better, we'll be taking a few examples below.

Example #1

In this first example, let's use a real-life example to see how this can be applied to our daily life. In this scenario, let's envision ourselves as teachers. We teach four different classes with a different number of students in each class.

We want our students to partner up into groups of 2, 3, 4, and 5. So in the first class, we have 20 students; in the second, we have 25; in the third, we have 30; and in our last lesson, 35 students.

1. Our (number) in this scenario is the number of students in the class, as represented by the Number column on the left. 

• 20
• 25
• 30
• 35

2. Our (number_chosen) in this scenario is the number of groups the students are to be paired into, represented by the second column from the left, “Chosen Number.”

• 2
• 3
• 4
• 5

3. Our Result is what the function returns, which means how many possible group combinations each class has.

• Class 1 has a possibility of 190 different groups.
• Class 2 has a possibility of 2,310 different groups.
• Class 3 has a possibility of 27,405 different groups.
• Class 4 has a possibility of 32,4632 different groups.

4. We implemented each number in the correct arguments for our formula to find our outcome. 

Example #2

Let's use another simple example to increase our understanding. In this scenario, we have four shirt color options to wear. We want to see how many combinations we can get from these shirts by layering two pieces by color.

Options:

1. 5 blue shirts, layering two.
2. 10 red shirts, layering two.
3. 6 pink shirts, layering two.
4. 8 green shirts, layering two.

1. The number of shirts in each color represents the (Number) in this scenario.

• 5
• 10
• 6
• 8

2. The (Number_Chosen) in this scenario is represented by the number of shirts they want to layer. 

• 2

3. Our result is what our function returns, which means all the combinations that can be worn for each color when layering.

• 10 combinations of layered blue shirts
• 45 combinations of layered red shirts
• 15 combinations of layered pink shirts
• 28 combinations of layered green shirts.

4. We implemented each number in the correct arguments for our formula to find our outcome.

Example 3

In this third example, we can see how this can be applied in the workspace using excel. Let's say we have a list of 1 to 10 items. How many combinations can I get from this if I divide them into groups of three? Again, COMBIN can do this very quickly.

1. The (Number) in this scenario is represented by our list of 10.

• 10

2. The groups represent the (Number_Chosen) in this scenario; we wanted to divide it into

• 3

3. Our result is what our function returns. Meaning all the combinations we can get when we divide the list of 10 into 3 combinations.

• 120 combinations

The Math Behind the COMBIN Function

Even though excel calculates your combinations for you with this function, it might be helpful to understand the math behind it to increase comprehension.

The formula for the COMBIN Function is:

• N = number
• K = number_chosen

An easy way to look at the combination formula is by working backward. "2 3 4" could be solved this way:

4! = 4 x 3 × 2 = 24

This works because, in combinations, the order doesn't matter. Therefore we can choose how to reduce our objects. 

COMBIN Function Common Errors

When using Excel functions for the first time, it's very common to get repeated errors. These errors are easy to make and cause frustration. Excel uses errors to communicate a restriction in the function. This is helpful in computing equations.

To avoid this, here are some of the most common errors when using the COMBIN Function:

1. #VALUE! Error

This error occurs when entering a non-numerical value when a function only accepts numeric values. For example, if a non-numeric value is entered in the (number,_) or (_, number_chosen) parameter, it will produce the #VALUE! Error.

The #VALUE! Error can occur with any function that requires the use of numbers only.

2. #NUM Error

This error occurs when a number is out of the constraint. If you remember, COMBIN has specific rules for each digit used in the argument. This error will occur if

• (Number,_) < 0
• (_, number_chosen) < 0 OR (_, number_chosen) > (Number,_)

Users typically make this error when clicking on the wrong cell. The #NUM error can be made with any function with inequality restrictions such as (<,>, or =). Therefore, it’s essential to know what each argument in the function serves to avoid this mistake.

3. Error Examples

• =COMBIN(-5,2) = #NUM!
• =COMBIN(5,6) = #NUM!
• =COMBIN(10,20) = #NUM!
• =COMBIN(“TEXT”,8) = #VALUE!
• =COMBIN(9,”TEXT”) = #VALUE!
• =COMBIN(“TEXT”,”TEXT”) = #VALUE!

Researched and authored by Emily Rustom | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• BINOM.DIST.RANGE Function
• CELL Function
• EXACT Function
• F.INV Function
• HOUR Function