Today, we are going to learn about the Excel COMBIN function. This tutorial will provide a complete overview of the COMBIN function in detail, the objective of this function, and some relatable real-life examples about the uses of the COMBIN function in Excel.
So. let’s start!
Excel COMBIN Function Overview
In mathematical calculations, Combination is defined as a technique of selecting items from a provided list of items without considering the order of selection. Don’t confuse Combination with Permutation! The main difference between Combination and Permutation is that Permutation takes the sequence of order into account, whereas Combination ignores the order.
COMBIN function is a Math and Trigonometry type function for performing mathematical calculations in Excel. This function determines the possible number of combinations from a given data range.
COMBIN function is used to determine the total possible number of combinations for a given number of items without any repetition in Excel.
|number||Required||The number of items. It should be greater than or equal to zero as well as number_chosen. Non-integer values are truncated if entered into this parameter.|
|number_chosen||Required||The number of items in each combination. It must be greater than or equal to zero. Non-integer values are truncated for this parameter also.|
Returns the number of combinations for a given number of items.
COMBIN function was first introduced in MS Excel in version 2000. So it is workable from this version and later on. I have used MS 365 for this article. You may proceed with your functional version.
Let’s say five friends named Mike, Adam, Stuart, David, and Parker want to play games by forming a team.
The total number of players here is 5. The players can form teams with different numbers (i.e. starting from 1 to 5) of members in a group. We want to find out how they can form teams with 2 players in a group.
So. let’s check how we can perform this task. In this section, we will see the use of the COMBIN function for finding the possible number of combinations of players. I will demonstrate them here with proper illustrations. Let’s check them now!
1. Calculating the Number of Combinations Directly with the COMBIN Function
In the concerning dataset, we have seen that there are 5 players in total. Our aim is to form some teams with 2 members in a group. So we need to find out the possible number of combinations of 2 players out of 5. For this, we will apply the COMBIN function. You can just directly input the parameters into the arguments of the function. Let’s proceed with the following steps.
- First of all, select a cell where you want to find the possible number of combinations from your dataset.
- Then, type the following formula in that cell:
- 5= Number (i.e. total number of players)
- 2= Number Chosen (i.e. number of players in a group)
- Then, press ENTER. The cell will auto-calculate the number of combinations from the input parameters of the COMBIN function. You can see that the cell has shown 10 as output. That means there are 10 possible groups having 2 members in each of the groups.
Just think with real-life examples, and you will find the 10 possible groups. We have shown the 10 duos with the 5 members of our dataset.
2. Using Excel COMBIN Function with Cell Reference
In the previous dataset, we used the value in the argument directly. We can also use the cell reference in the argument of the function. In our previous dataset, the number of players was 5 in total. Now, we will calculate the possible teams starting from 1 to 5 in each group.
Follow the steps below for this purpose.
- Firstly, select a cell for finding the number of possible teams with 1 member in each group.
- Then, type the following formula in the cell.
- $E$7= Number (i.e. total number of players)
- B5= Number Chosen
- Then, press ENTER and you will see that 5 teams are possible with 1 member in each group. This definitely makes sense, and you don’t even need to overthink that.
- Now, use the Fill Handle tool to copy the formula for finding the other groups with members 2, 3, 4, and 5 in each of them. Just drag the tool downward or double-click it.
- Hence, you will get all the possible combinations with corresponding chosen numbers.
3. Nesting COUNTA Function with COMBIN Function
Sometimes, you may not have the numbers directly that you need to input as the argument; rather, you have a clue, and you need to find out the numbers first.
In that case, using the COUNTA function can be a fruitful way to serve your purpose. The COUNTA function counts the total number of non-empty cells in a given range. So, using the COUNTA function nested in the COMBIN function will act as an argument for the COMBIN function.
- First, type the following formula to find the number of combinations of 2 members in each group.
Here, the COUNTA function counts the total number of cells within the range (C5:C9). So, it counts the total number of players in that range. We want to create teams with 2 players and so we used the second argument 2.
- Next, press ENTER and you will get your desired output.
4. Using the COMBIN Function in VBA Code
You can also use the COMBIN function in a VBA code to find the same output for the concerned dataset.
- First of all, press ALT+F11 to activate the Visual Basic Editor window.
- Now, Insert a new Module and apply the following code in the Module window.
Sub use_COMBIN_function() Dim rfCombin As Double rfCombin = Application.WorksheetFunction.Combin(5, 2) MsgBox (rfCombin) End Sub
Here, we have applied code for creating groups of 2 items out of 5.
- Then, press ALT+Q to switch back to the worksheet.
- Next, press ALT+F8 to open the Macro dialog box-> select the Macro Name and click Run.
- The output will be shown in a message box finally.
💡 Quick Notes
- The function returns #VALUE! error when you apply non-numerical value as an argument.
- The formulated cell will show #NUM! error when the first argument is less than 0 or Negative and the second argument is negative or greater than the first argument.
- COMBIN function ignores repetition. If the sequence of the order needs to be considered, then you need to use the PERMUT function instead of the COMBIN function, as the order is significant for the PERMUT function.
📁 Download Practice Workbook
You can download the practice book from the link below.
In this tutorial, I have tried to show you some ways of using the COMBIN function in Excel. Hope you like reading the article and, from now on, you can apply this function to serve your purpose. Don’t forget to leave a comment if you have any queries or feedback regarding this article. Happy Excelling!!!