COMBIN Excel function 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.
COMBIN in Excel (Table of Contents)
COMBIN in Excel
COMBIN function is categories as Math or Trig function in excel returns the number of combinations for a specified number of items. So you can use this function to calculate the combinations of a number in Excel. Use COMBIN to determine the total possible number of groups for a given number of items.
The Combination function in excel is first introduced in version 2000.
COMBIN Formula in Excel
Below is the COMBIN Formula in Excel
The COMBIN formula in excel helps to derive such combinations as shown above. The number of combinations is as follows, where number = n and number_chosen = k:
Explanation of COMBIN Function in Excel
COMBIN function in excel has two compulsory parameters i.e. number and number_chosen.
- 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?
COMBIN in excel is very simple and easy to use. Let understand the working of COMBIN function in excel by some examples. COMBIN function in excel can be used as a worksheet function and as VBA function.
COMBIN function in Excel as a worksheet function.
COMBIN in Excel Example #1
For any 6 objects (e.g. a, b, c, d, e, f), there are 15 different combinations of 2 objects.
And can be calculated easily by COMBIN formula in excel like as follows:
will get 15 combinations
COMBIN in Excel 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’s use COMBIN formula in excel 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 in excel is shown in the result column.
COMBIN in Excel 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 in excel, we can see the possible two person teams that can be formed from 20 employees.
Here we use COMBIN function in excel to achieve this
=COMBIN (20, 2)
the output will be 190
COMBIN in Excel Example #4
This time consider the 5 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.
Manual calculation on possible combinations as follows:
First make the combination 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 in last with D as shown in column 4 with out repetition.
Now calculate every possible combination using =COMBIN(K17,K18).
We will get 10:
COMBIN in Excel Example #5
Combination in excel can be used as a VBA function.
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 Message box.
Output “5245786” will be printed in the message box.
Things to Remember about the COMBIN Function in Excel
- Arguments that contain decimal values are truncated to integers.
- If the supplied number is nonnumeric value then COMBIN function will return the #VALUE! Error.
- If the supplied number_chosen is nonnumeric value then 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.
This has been a guide to COMBIN Function in Excel. Here we discuss the COMBIN Formula in excel and how to use Combination function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –