Part 1: Introduce
Definition:
The COMBIN function in Microsoft Excel calculates the number of combinations for a given set of items.
Purpose:
The function determines the total possible number of groups for a given number of items.
Syntax & Arguments:
syntax
COMBIN(number, number_chosen) - Number: The total number of items.
- Number_chosen: The number of items in each combination.
Explain the Arguments in the function:
- Number: This is a required argument. It represents the total number of items you have.
- Number_chosen: Also a required argument. It signifies the number of items you want in each combination.
Return value:
The function returns the number of possible combinations.
Remarks:
- Numeric arguments are truncated to integers.
- If either argument is nonnumeric, COMBIN returns the #VALUE! Error value.
- If the number is less than 0, number_chosen is less than 0, or the number is less than number_chosen, COMBIN returns the #NUM! Error value.
- A combination is any set or subset of items, regardless of their internal order. This is different from permutations, where the order matters.
Part 2: Examples
Example 1:
- Purpose of example: Determine the number of two-person teams from 8 candidates.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Total Candidates | 8 | |
| 3 | Team Size | 2 | |
| 4 | Formula | =COMBIN(A2, A3) | |
| 5 | Result | 28 |
- Explanation: From 8 candidates, there are 28 possible two-person teams.
Example 2:
- Purpose of example: Calculate the number of ways to choose 3 projects from a list of 10.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Total Projects | 10 | |
| 3 | Projects Chosen | 3 | |
| 4 | Formula | =COMBIN(A2, A3) | |
| 5 | Result | 120 |
- Explanation: From 10 projects, there are 120 ways to choose 3.
Example 3:
- Purpose of example: Determine the number of ways to select 4 products for a promotional bundle from a list of 12 products.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Total Products | 12 | |
| 3 | Products Chosen | 4 | |
| 4 | Formula | =COMBIN(A2, A3) | |
| 5 | Result | 495 |
- Explanation: From 12 products, there are 495 ways to select 4 for a promotional bundle.
Example 4:
- Purpose of example: Calculate the number of combinations to form 5-member committees from 15 employees.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Total Employees | 15 | |
| 3 | Committee Size | 5 | |
| 4 | Formula | =COMBIN(A2, A3) | |
| 5 | Result | 3,003 |
- Explanation: From a group of 15 employees, there are 3,003 ways to form a 5-member committee.
Example 5:
- Purpose of example: Determine the number of ways to select 6 items for a sample sale from a collection of 20 items.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Total Items | 20 | |
| 3 | Items Chosen | 6 | |
| 4 | Formula | =COMBIN(A2, A3) | |
| 5 | Result | 38,760 |
- Explanation: From a collection of 20 items, there are 38,760 ways to select 6 for a sample sale.
Example 6:
- Purpose of example: Calculate the number of ways to select 3 team leaders from 10 candidates, but only if there are more than 5 candidates.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | Total Leaders | 10 | ||
| 3 | Leaders Chosen | 3 | ||
| 4 | Formula | =IF(A2>5, COMBIN(A2, A3), "N/A") | ||
| 5 | Result | 120 |
- Explanation: This example uses the
IFfunction to check if there are more than 5 leaders. If accurate, it calculates the number of ways to select 3 leaders from 10 using theCOMBINfunction, resulting in 120 possible combinations. If not, it returns “N/A”.
Example 7:
- Purpose of example: Sum the combinations of choosing 2 products from 3 different product lines, each having other numbers of products.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Line | |||
| 2 | Product Line 1 | 5 | =COMBIN(B2, 2) | 10 |
| 3 | Product Line 2 | 6 | =COMBIN(B3, 2) | 15 |
| 4 | Product Line 3 | 7 | =COMBIN(B4, 2) | 21 |
| 5 | Total Combinations | =SUM(C2:C4) | 46 |
- Explanation: This example uses the
SUMfunction to add up the combinations of choosing 2 products from each product line. TheCOMBINfunction calculates the combinations for each product line separately.
Example 8:
- Purpose of illustration: Determine the number of ways to pair up items from two different lists, but only if the item from the first list exists in the second list using
VLOOKUP. - Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | List 1 | List 2 | ||
| 2 | Item 1 | Apple | Apple | 1 |
| 3 | Item 2 | Banana | Orange | 0 |
| 4 | Item 3 | Orange | Banana | 0 |
| 5 | Formula | =COMBIN(IF(VLOOKUP(B2:C4, B2:C4, 1, FALSE), 1, 0), 2) | ||
| 6 | Result | 1 |
- Explanation: This example uses the
VLOOKUPfunction to check if an item from List 1 exists in List 2. If it does, theCOMBINfunction calculates the number of ways to pair up the items.
Example 9:
- Purpose of illustration: Calculate the bonus for sales teams based on the possible team combinations they can form. Groups with more combinations get a higher bonus using the
IFfunction. - Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team Size | |||
| 2 | Team A | 7 | =IF(COMBIN(B2, 3)>10, "$500", "$300") | $500 |
| 3 | Team B | 4 | =IF(COMBIN(B3, 3)>10, "$500", "$300") | $300 |
- Explanation: Teams that can form more than 10 combinations of 3 members get a bonus of $500, while others earn $300. The
COMBINfunction calculates the number of combinations and theIFfunction determines the premium.
Example 10:
- Purpose of illustration: Sum the number of ways to form pairs from three different departments using the
SUMfunction. - Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | |||
| 2 | Dept 1 | 6 | =COMBIN(B2, 2) | 15 |
| 3 | Dept 2 | 5 | =COMBIN(B3, 2) | 10 |
| 4 | Dept 3 | 7 | =COMBIN(B4, 2) | 21 |
| 5 | Total Pairs | =SUM(C2:C4) | 46 |
- Explanation: The
COMBINfunction calculates the number of ways to form pairs from each department. TheSUMfunction then adds these combinations to give the total number of pairs across all departments.
Example 11:
- Purpose of illustration: Determine the number of ways to select products for a promotional offer based on their ranking using the
RANKfunction. - Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Ranking | Combos | |
| 2 | Product 1 | Laptop | 500 | =RANK(C2, C2:C4) | =COMBIN(D2, 2) |
| 3 | Product 2 | Phone | 600 | =RANK(C3, C2:C4) | =COMBIN(D3, 2) |
| 4 | Product 3 | Headset | 300 | =RANK(C4, C2:C4) | =COMBIN(D4, 2) |
- Explanation: Products are ranked based on their sales using the
RANKfunction. TheCOMBINfunction then calculates the number of ways to select products based on their ranking.
Example 12:
- Purpose of illustration: Calculate the number of ways to select team members based on their performance score using the
AVERAGEfunction. - Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Member | Score | Average | Combos | |
| 2 | Member 1 | John | 85 | =AVERAGE(C2:C4) | =COMBIN(D2, 2) |
| 3 | Member 2 | Jane | 90 | =AVERAGE(C2:C4) | =COMBIN(D3, 2) |
| 4 | Member 3 | Bob | 80 | =AVERAGE(C2:C4) | =COMBIN(D4, 2) |
- Explanation: The performance score of each team member is averaged using the
AVERAGEfunction. TheCOMBINfunction then calculates the number of ways to select team members based on their average score.
Part 3: Tips and tricks
- Always ensure that the number chosen is less than or equal to the total number. Otherwise, you’ll get an error.
- The COMBIN function only considers combinations, not permutations. If order matters, you might need a different function.
- Use the COMBIN function in scenarios like team formations, project selections, or any situation where you need to find out the number of ways to choose items from a more extensive set.