COMBIN Function in Excel (Table of Contents)
COMBIN in Excel
Combin function in excel is used to combine sets of a specified number. This function is categorized in mathematical trigonometry where we need to select two numbers where the first number should be greater than 0 and also it should be greater than the number is chosen and the second number which is Chosen Number it should also be greater than the first number and non-integer.
COMBIN Formula in Excel:
Below is the COMBIN Formula in Excel.
Where both the arguments are compulsory parameters, it needs to be entered to get the desired result.
- Number or n: It is a number of items or it is a number of objects in the set
Note: It should be equal or greater than the number_chosen.
- Number_chosen or k: It is the number of items in a combination or It is a number of items in each of the combinations
How to Use the COMBIN Function in Excel?
COMBIN Function in Excel can be used as a worksheet function as well as a VBA Function. Here are some examples of COMBIN function to understand the working of COMBIN function in Excel.
Example #1
Suppose I have six items i.e. K, L, M, N, O, P. I want in a combination of 2. Here I want to know the combinations of two-letter that are possible from a list (K, L, M, N, O, P). It can be calculated easily by COMBIN formula.
Select the cell D10 where COMBIN function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “COMBIN” in the search for a function box, COMBIN function will appear in select a function box. Double click on COMBIN function.
A dialog box appears where arguments (number & number_chosen) for COMBIN function needs to be filled or entered.
Here number argument is 6, Whereas number_chosen value is 2.
i.e. =COMBIN(6,2)is used to find out the possible number of combinations.
It results or returns the value 15.
15 below mentioned combination is possible.
Here, elements internal order is not important, that’s the reason [K, L] and [L, K] is the same combination. Now let’s use the COMBIN formula in excel to find out the number of combinations without repetitions for the other numbers of objects taken from a set of 6.
Example #2
I have numbers from 1 to 7 in the excel sheet. i.e. 1, 2, 3, 4, 5, 6, 7. I want in a combination of 2. Here I want to know the combinations of two-digit that are possible from a list (1, 2, 3, 4, 5, 6, 7). It can be calculated easily by COMBIN formula.
Select the cell D7 where COMBIN function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “COMBIN” in the search for a function box, COMBIN function will appear in select a function box. Double click on COMBIN function.
A dialog box appears where arguments (number & number_chosen) for COMBIN function needs to be filled or entered.
Here number argument is 7, Whereas number_chosen value is 2.
i.e. =COMBIN(7,2) is used to find out the possible number of combinations.
It results or returns the value 21.
21 below mentioned combination is possible.
Here, numbers internal order is not important, that’s the reason [1,2] and [2,1] is the same combination. Now let’s use the COMBIN formula in excel to find out the number of combinations without repetitions for the other numbers of objects taken from a set of 7.
Example #3
In this example, I need to find out how many teams are possible in a group of three from nine players?. I have the total number of players here. i.e. 9. I want in a combination of 3. Here I want to know the combinations of three-digit that are possible from 9 players. It can be calculated easily by COMBIN formula.
Select a cell “C9” where COMBIN function is applied.
From the Math & Trig Functions menu, select COMBIN to open its Function Arguments dialog box.
In the Function Arguments dialog box, type the appropriate values for the arguments.
In the Number argument, enter 9. In the Number_chosen argument, enter 3.
i.e. =COMBIN(9,3)
With values entered for both arguments, the answer 84 appears in the dialog box.
Things to remember about the COMBIN Function in Excel
- Note that, if any of the arguments i.e. (number, number_chosen) in COMBIN function are supplied as decimal values, they are truncated to integers by the COMBIN function.
- In Combin function,COMBIN (number, number_chosen), if number & number_chosen argument are NON-NUMERIC VALUE, then COMBIN function will return #VALUE! Error.
- #NUM error occurs if the supplied number argument is less than 0 or negative value or if a chosen argument is less than 0 or is greater than the number argument.
E.G
=COMBIN (-1,2) = #NUM!
=COMBIN (1,2) = #NUM!
=COMBIN(“TEXT”,2) = #VALUE!
=COMBIN(2,”TEXT”) = #VALUE!
- If either of the argument i.e. number_chosen or number argument in combin function is less than 0, then the COMBIN function will return the #NUM! Error.
- Excel COMBIN and COMBIN function both calculate a number of possible combinations of a set of objects or a number of items. The only difference between these functions is COMBIN function counts repetitions of combinations whereas COMBIN function does not count a repetition.
Example:
In a set of 3 objects, X, Y, Z. how many combinations of 2 objects are there?. The Combin function returns the result 3 (combinations: XY, XZ, YZ);
Whereas, The COMBIN function returns the result 6 (combinations: XX, XY, XZ, YY, YZ, ZZ)
- A combination is any subset of items or set, regardless of their internal order. Combinations differ from permutations, in permutations, the internal order is significant.
- In COMBIN function, an internal order is not important.
Recommended Articles
This has been a guide to COMBIN in Excel. Here we discuss the COMBIN Formula in Excel and How to use COMBIN Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
