COMBIN Function in Excel (Table of Contents)
COMBIN in Excel
- COMBIN function is used as a worksheet function in excel. It’s an in-built function in excel that is categorized under math/trig function.
- It is useful to determine or derive at the number of combinations for a specific total of items.
- This function is easy to use & very simple.
- It returns the possible number of combinations for a specified number of items without repetition. or
- The end result, it returns the total possible combinations for the number of items & number chosen.
- Combin function does not consider repetitions of the same item.
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 – COMBIN Function For Text Value
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 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 – COMBIN Function For Numeric Value
I have numbers from 1 to 7 in 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 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 number of items. The only difference between these functions is COMBIN function counts repetitions of combinations whereas COMBIN function does not count a repetition.
E.G.
For 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 –
