COMBIN Function in Excel
Last Updated :
21 Aug, 2024
Blog Author :
Edited by :
Ashish Kumar Srivastav
Reviewed by :
Dheeraj Vaidya, CFA, FRM
Table Of Contents
COMBIN in Excel
The COMBIN function in Excel is also known as the combination function as it calculates the number of possible combinations for two given numbers. This function takes two arguments: the number and the number_chosen.
For example, if the number is 5 and the number chosen is 1, 5 combinations give 5.
Table of contents
Syntax
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 critical 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: number_chosen is the number of items in each combination 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 with some examples. It can be used as a worksheet function and as a VBA function.
Example #1
For any six objects (e.g., a, b, c, d, e, f), there are 15 different combinations of 2 objects.
These are:
And can be calculated easily by the COMBIN function as follows:
=COMBIN(6,2)
As a result, we will get 15 combinations.
Example #2
Suppose we are given ten numbers from 1 to 10 as 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10.
Now, let us use the COMBIN function to calculate the number of combinations without repetitions for the other numbers of objects taken from a set of 10.
The output of the COMBIN is shown in the result column.
Example #3
Let us now see how we can use this function in day-to-day life. For example, suppose there are 20 employees, and we want to pair them into two-person teams. Then, using the COMBIN function, we can see the possible two-person teams formed from 20 employees.
=COMBIN (20, 2)
As a result, the output will be 190.
Example #4
The five objects are A, B, C, D, and E. Then, take a pair for each. Now, determine the possible combinations manually and use COMBIN in Excel separately, as shown below.
Manual calculation on possible combinations as follows:
First, we must combine with A, as shown in the first column, then with B, as shown in column 2, and then with C, as shown in column 3, and last with D, as shown in column 4, without repetition.
We must calculate every possible combination using =COMBIN(K17, K18).
Consequently, we will get 10.
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
Msgbox(dblCombin) //Print the output possible combinations in the Message box.
End sub
Output ā5245786ā will be printed in the message box.
Things to Remember
- Arguments that contain decimal values are truncated to integers.
- If the supplied number is non-numeric, the COMBIN function will return the #VALUE! Error.
- The COMBIN function will return the #VALUE! 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 argument is outside 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 article is a guide to COMBIN Function in Excel. We discuss its formula and how to use the COMBIN function, examples, and downloadable Excel templates. You may also look at these useful functions in Excel: -