COMBIN Function in Excel

COMBIN in Excel

COMBIN function in excel is also known as combination function which is used to calculate the number of possible combinations for two given  numbers, this function takes two arguments one is the number and number chosen, for example, if the number is 5 and number chosen is 1 then there are total of 5 combinations so it gives 5 as a result.

Syntax

COMBIN Formula

Remark

It helps to derive such combinations, as shown above. The number of combinations is as follows, where number = n and number_chosen = k:

Parameters

It has two compulsory parameters i.e., number and number_chosen.

Compulsory Parameter:

  • number: number should be greater than or equal to zero; also, it should be greater than or equal to the number_chosen.
  • number_chosen: It is a number of items in each of the combinations, and must be greater than or equal to zero.

How to Use COMBIN Function in Excel?

Let us understand the working of the COMBIN function in excel by some examples. It can be used as a worksheet function and as a VBA function.

You can download this COMBIN Function Excel Template here – COMBIN Function Excel Template

Example #1

For any six objects (e.g., a, b, c, d, e, f), there are 15 different combinations of 2 objects.

These are:

COMBIN Example 1
And can be calculated easily by COMBIN function like as follows:

=COMBIN(6,2)

COMBIN Example 1-1

will get 15 combinations

COMBIN Example 1-2

Example #2

Suppose we are given ten numbers from 1 to 10 as 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10.

COMBIN Example 2

Now let’s use the COMBIN function to calculate the number of combinations without repetitions for the other numbers of objects taken from a set of 10.

COMBIN Example 2-1

The output of the COMBIN is shown in the result column.

COMBIN Example 2-2

Example #3

Let’s now see how we can use this function in day-to-day life. Suppose there are 20 employees, and we want to pair them into two-person teams. Using the COMBIN function, we can see the possible two-person teams that can be formed from 20 employees.

=COMBIN (20, 2)

COMBIN Example 3

the output will be 190

COMBIN Example 3-1

Example #4

This time consider the five objects as a, b, c, d, and e. And take a pair for each as shown below. Now determine the possible combinations manually and using COMBIN in excel separately, as shown in the below tables.

data table

Manual calculation on possible combinations as follows:

Manual calculation on possible combinations

First, make the combination with A as shown in the first column than with B, as shown in column 2 and then with C, as shown in column 3 and in last with D as shown in column 4 without repetition.

Now calculate every possible combination using =COMBIN(K17,K18).

calculate total possible combination

We will get 10:

Total Possible Combination

Example #5

A combination in excel can be used as a VBA function.

Sub usefunction()

Dim dblCombin As Double // declare a variable as double

dblCombin = Application.WorksheetFunction.Combin(42 , 6) //save the combination in excel o/p in dblcombin variable

COMBIN

Msgbox(dblCombin)  //Print the output possible combinations in Message box.

End sub

Output 5245786” will be printed in the message box.

Message box

Things to Remember

  • Arguments that contain decimal values are truncated to integers.
  • If the supplied number is a non-numeric value, then the COMBIN function will return the #VALUE! Error.
  • If the supplied number_chosen is a non-numeric value, then the COMBIN function will return the #VALUE! Error.
  • #NUM error – Occurs when the value or any argument is outside of its constraint.
    • the supplied number argument is less than 0;
    • The supplied number_chosen argument is less than 0 or is greater than the number argument.

Recommended Articles

This has been a guide to COMBIN Function in Excel. Here we discuss the COMBIN Formula in excel and how to use the Combination function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>