Updated May 5, 2023
COMBIN Function in Excel (Table of Contents)
COMBIN in Excel
The combin’ function in Excel combines sets of a specified number. This function is categorized in mathematical trigonometry, where we must select two numbers where the first number should be greater than 0. Also, it should be greater than the number chosen, and the second number, Chosen Number, should also be greater than the first number and non-integer.
COMBIN Formula in Excel:
Below is the COMBIN Formula in Excel.
Where both arguments are compulsory parameters, it needs to be entered to get the desired result.
- Number of n: It is a number of items, or it is a number of objects in the set
Note: It should be equal to or greater than the number chosen.
- Number_chosen or k: It is the number of items in a combination of 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 the COMBIN function to understand the working of the COMBIN function in Excel.
Example #1
Suppose I have six items, i.e. K, L, M, N, O, P. I want 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 the COMBIN formula.
Select cell D10 where the 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 the select function box. Double click on the COMBIN function.
A dialog box appears where arguments (number & number_chosen) for the COMBIN function need to be filled or entered.
Here number argument is 6, Whereas the number_chosen value is 2.
i.e. =COMBIN(6,2)is used to find out the possible number of combinations.
It results in or returns the value of 15.
15 below-mentioned combination is possible.
Here, the elements’ internal order is not important; that’s why [K, L] and [L, K] are 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 a combination of the 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 the COMBIN formula.
Select cell D7 where the 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 the select a function box. Double click on the COMBIN function.
A dialog box appears where arguments (number & number_chosen) for the COMBIN function need to be filled or entered.
Here number argument is 7, Whereas the number_chosen value is 2.
i.e. =COMBIN(7,2) is used to find out the possible number of combinations.
It results in or returns the value 21.
21 below-mentioned combination is possible.
Here, the numbers’ internal order is not important; that’s why [1,2] and [2,1] are 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 a combination of 3. Here I want to know the combinations of three-digit that are possible from 9 players. The COMBIN formula can easily calculate it.
Select a cell “C9” where the 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.
Enter 9 in the Number parameter. In the Number_chosen argument, enter 3.
i.e. =COMBIN(9,3)
With values entered for both arguments, 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 the COMBIN function are supplied as decimal values, they are truncated to integers by the COMBIN function.
- In the Combin function, COMBIN (number, number_chosen), if the number & number_chosen argument are NON-NUMERIC VALUE, then the 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 the combin function is less than 0, then the COMBIN function will return the #NUM! Error.
- Excel COMBIN and COMBIN functions 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 the 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 the 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 a downloadable Excel template. You can also go through our other suggested articles –
