# How to use the COMBIN function

The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of elements.

#### Table of Contents

## 1. Syntax

COMBIN(*number*, *number_chosen*)

number |
Required. A whole number larger than 0 (zero) represents the total number of elements. |

number_chosen |
Required. A whole number larger than 0 (zero) represents the number of elements in each combination. |

I recommend you press with left mouse button on the following link if you want to read about the difference between combinations and permutationsÂ in greater detail.

## 2. Example

**You have 3 different types of fruits and want to make a fruit salad with 2 fruits. If you can't use the same fruit more than once (no repetition), how many different combinations are possible?**

Here are the arguments:

- number: 3
- number_chosen: 2

Formula in cell F3:

The formula in cell F3 returns 3 combinations. Column B, C and D demonstrate how many combinations there are when 2 elements are selected out of 3 elements [A, B, C].

The three combinations are [A,B] ,[A,C] and [B,C]. The elements' internal order is not important, that is why [A,B] and [B,A] is the same combination.

The math formula behind the COMBIN function is:

C(n,r) = n!/(r!(n-r)!)

C = Combinations

n = objects (*number*)

r = sample (*number_chosen*)

Lets use the values given in the question and manually calculate the value.

3!/(2!(3-2)!)

=3*2/2*1

=6/2

=3

The math formula calculates number 3 which represents the number of combinations with 3 objects and 2 chosen. This value matches the calculated value in cell F3.

## 3. COMBIN Function alternative

Here is how the COMBIN function calculates in greater detail, see the image above. The formula below is the same as the formula shown in the image above, FACT function is the ! (factorial character).

The text representation is:

C(n,r) = n!/(r!(n-r)!)

C = Combinations

n = objects (*number*)

r = sample (*number_chosen*)

Formula in cell C7:

### 3.1 Explaining formula

#### Step 1 - Calculate the numerator

The FACT function calculates the factorial of a number.

FACT(*number*)

FACT(C5)

becomes

FACT(3)

and returns 6. 3*2*1 equals 6.

#### Step 2 - Calculate the denominator

(FACT(C6)*FACT(C5-C6))

C5-C6

becomes

3-2 and returns 1.

FACT(1) is 1.

FACT(2) is 2. 2*1 equals 2.

The parentheses let you control the order of operation.

(FACT(C6)*FACT(C5-C6))

becomes

2*1 equals 2.

#### Step 3 - Calculate the division

FACT(C5)/(FACT(C6)*FACT(C5-C6))

becomes

6/2 equals 3.

## 4. List combinations - Excel 365 formula

This section demonstrates how to list all combinations based on n (numbers) and r (numbers_chosen). The following formula works only in Excel 365 and it spills values to the right and to cells below as far as needed.

Check out this article: Return all combinations that describes a User Defined Function that lists combinations. That UDF should work for most Excel versions.

Excel 365 dynamic array formula in cell I2:

The formula has three arguments:

- objects: C2:C7 (This populates the list of combinations with the given values, this example uses A to F.
- n: F2 (The total number of objects, this value must match the number of values in argument objects)
- r: F3 (The number chosen)

The image above shows a table in cell range I2:N16 containing all possible combinations with 4 objects chosen from a group of 6 objects in total. The formula populates the table with the specified values in C2:C7 accordingly.

The table is built dynamically meaning if you change the input arguments the table changes it size almost instantly. Make sure you use the same number of values in cell range C2:C7 as the specified number in cell F2.

### 4.1 Explaining formula

#### Step 1 - To the power of

This step calculates the number of rows needed to calculate every combination.

2^F2

becomes

2^6 equals 64.

#### Step 2 - Create an array from 0 (zero) to n

The SEQUENCE function creates a sequence of numbers.

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(2^F2)-1

becomes

SEQUENCE(64)-1

becomes

{1; 2; 3; ... ; 64} - 1

and returns

{0; 1; 2; 3; ... ; 63}.

#### Step 3 - Divide vertical array by horizontal array

(SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)

becomes

{0; 1; 2; 3; ... ; 63}/2^SEQUENCE(,F2,0)

becomes

{0; 1; 2; 3; ... ; 63}/2^{0,1,2,3,4,5}

becomes

{0; 1; 2; 3; ... ; 63}/{1,2,4,8,16,32}

and returns

{0, 0, 0, 0, 0, 0;1,Â ... , 1.96875}.

#### Step 3 - Remove decimals

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

INT(*number*)

INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0))

becomes

INT({0, 0, 0, 0, 0, 0;1,Â ... , 1.96875})

and returns

{0, 0, 0, 0, 0, 0; 1, ... , 1}

#### Step 4 - Calculate the remainder

The MOD function returns the remainder after a number is divided by a divisor.

MOD(*number*,Â *divisor*)

MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2)

becomes

MOD({0, 0, 0, 0, 0, 0; 1, ... , 1}, 2)

and returns

{0, 0, 0, 0, 0, 0; 1, ... , 1}.

#### Step 5 - Calculate the matrix product of two arrays

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(*array1, array2*)

MMULT(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(, F2,0)), 2), SEQUENCE(F2)^0)

becomes

MMULT({0, 0, 0, 0, 0, 0; 1,Â ... , 1}, {1; 1; 1; 1; 1; 1})

and returns

{0; 1; 1; 2; 1;Â ... ; 6}

#### Step 6 - Check if number is equal to *numbers_chosen*

The equal sign lets you compare value to value in an Excel formula, the result is a boolean value TRUE or FALSE.

MMULT(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),SEQUENCE(F2)^0)=F3

becomes

{0; 1; 1; 2; 1;Â ... ; 6}=4

and returns

{FALSE; FALSE; FALSE; FALSE;Â ... ; FALSE}

#### Step 7 - Filter combinations based on the number chosen

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*,Â *include*, [*if_empty*])

FILTER(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),MMULT(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),SEQUENCE(F2)^0)=F3)

becomes

FILTER(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),{FALSE; FALSE; FALSE; FALSE;Â ... ; FALSE})

becomes

FILTER({0, 0, 0, 0, 0, 0; 1, ... , 1},{FALSE; FALSE; FALSE; FALSE;Â ... ; FALSE})

and returns

{1, 1, 1, 1, 0, 0;

1, 1, 1, 0, 1, 0;

1, 1, 0, 1, 1, 0;

1, 0, 1, 1, 1, 0;

0, 1, 1, 1, 1, 0;

1, 1, 1, 0, 0, 1;

1, 1, 0, 1, 0, 1;

1, 0, 1, 1, 0, 1;

0, 1, 1, 1, 0, 1;

1, 1, 0, 0, 1, 1;

1, 0, 1, 0, 1, 1;

0, 1, 1, 0, 1, 1;

1, 0, 0, 1, 1, 1;

0, 1, 0, 1, 1, 1;

0, 0, 1, 1, 1, 1}

#### Step 8 - Populate the array

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(FILTER(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),MMULT(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),SEQUENCE(F2)^0)=F3),TRANSPOSE(C2:C7),"")

becomes

IF({1, 1, 1, 1, 0, 0;

1, 1, 1, 0, 1, 0;

1, 1, 0, 1, 1, 0;

1, 0, 1, 1, 1, 0;

0, 1, 1, 1, 1, 0;

1, 1, 1, 0, 0, 1;

1, 1, 0, 1, 0, 1;

1, 0, 1, 1, 0, 1;

0, 1, 1, 1, 0, 1;

1, 1, 0, 0, 1, 1;

1, 0, 1, 0, 1, 1;

0, 1, 1, 0, 1, 1;

1, 0, 0, 1, 1, 1;

0, 1, 0, 1, 1, 1;

0, 0, 1, 1, 1, 1},TRANSPOSE(C2:C7),"")

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.

TRANSPOSE(*array*)

TRANSPOSE(C2:C7)

becomes

TRANSPOSE({"A"; "B"; "C"; "D"; "E"; "F"})

and returns

{"A", "B", "C", "D", "E", "F"}.

IF({1, 1, 1, 1, 0, 0;

...

0, 0, 1, 1, 1, 1},TRANSPOSE(C2:C7),"")

becomes

IF({1, 1, 1, 1, 0, 0;

...

0, 0, 1, 1, 1, 1},{"A", "B", "C", "D", "E", "F"},"")

and returns

{"A", "B", "C", "D", "", "";

"A", "B", "C", "", "E", "";

"A", "B", "", "D", "E", "";

"A", "", "C", "D", "E", "";

"", "B", "C", "D", "E", "";

"A", "B", "C", "", "", "F";

"A", "B", "", "D", "", "F";

"A", "", "C", "D", "", "F";

"", "B", "C", "D", "", "F";

"A", "B", "", "", "E", "F";

"A", "", "C", "", "E", "F";

"", "B", "C", "", "E", "F";

"A", "", "", "D", "E", "F";

"", "B", "", "D", "E", "F";

"", "", "C", "D", "E", "F"}

#### Step 9 - Simplify formula

The LET function lets you Â name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*,Â *name_value1*,Â *calculation_or_name2*,

[*name_value2*,Â *calculation_or_name3*...])

IF(FILTER(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),MMULT(MOD(INT((SEQUENCE(2^F2)-1)/2^SEQUENCE(,F2,0)),2),SEQUENCE(F2)^0)=F3),TRANSPOSE(C2:C7),"")

x - MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2)

y - F2

LET(y, F2, x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=F3), TRANSPOSE(C2:C7), ""))

## 5. Test combinations (Solver)

The Solver is an Excel feature that can test combinations in order to find the best combination based on a condition or criteria. This example shows parcel names in column B, weight in column C, and the value in column D.

Which combination of four parcels out of 11 has the highest value if the total weight is lower than or equal to 291?

Total weight formula in cell F16:

Total value formula in cell F17:

### 5.1 Explaining the total weight formula

#### Step 1 - Compare values

The equal sign is a logical operator that lets you check if a value is equal to another value, in this case, multiple values to a single value.

The Excel Solver changes the numbers, 1 or 0 (zero), in cells F3:F13. We use these values to calculate the total weight for each combination.

F3:F13=1

#### Step 2 - Control the order of operation

The parentheses lets you control the order of intermediate calculations, we want to compare F3:F13 to 1 before we multiply by C3:C13.

(F3:F13=1)

#### Step 3 - Multiply

The asterisk character lets you multiply values and arrays in an Excel formula.

(F3:F13=1)*C3:C13

#### Step 4 - Calculate a total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT(*array1*, [*array2]*, ...)

SUMPRODUCT((F3:F13=1)*C3:C13)

### 5.2 Setting up the Excel Solver

- Go to the tab "Data" on the ribbon.
- Press the left mouse button on the "Solver" button.
- Press the left mouse button on the arrow next to "Set Objective" and select cell F17. This value is F3:F13 multiplied by D3:D13 and then summarized.
- Press the left mouse button on the radio button named "Max" to select it. This lets the solver know that we are looking for a combination that returns the largest sum.
- Press with left mouse button on the arrow next to "By changing variable cells" and select cells F3:F13. These cells changes between 1 and 0 (zero).
- Press with the left mouse button on the "Add" button. A dialog box appears, this lets you apply constraints. Add the constraints shown in the image above specifiied below "Subject to the Constraints:".
- Change solving method to "Evolutionary".
- Press with the mouse on the check box "Make unconstrained variables Non-negative to enable it.
- Press with left mouse button on the "Solve" button to start.
- A dialog box appears after some time, press the left mouse button on the "OK" button.

### Useful links

COMBIN function - Microsoft

COMBIN function

### 'COMBIN' function examples

The following article has a formula that contains the COMBIN function.

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

### 'COMBIN' function examples

Table of Contents Identify numbers in sum using Excel solver Find numbers in sum - UDF Find positive and […]

This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]

This article demonstrates ways to use solver in Excel. Table of Contents Introduction Using Excel Solver to schedule employees Cash […]

### Functions in 'Math and trigonometry' category

The COMBIN function function is one of 61 functions in the 'Math and trigonometry' category.

### Excel function categories

### Excel categories

### 3 Responses to “How to use the COMBIN function”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

hello there,

this was very useful to me, thanks!

btw, =LET(y, F2, x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=F3), TRANSPOSE(C2:C7), ""))

works with office 2021 too,

can the formula be changed so it do three more things?

a-read the array from a horizontal array

b-ignore the blanks cells, don't write them down

c-combine all cells into one like a-b-c-d, a-b-c-e and etc

Hello mr_t

works with office 2021 tooThank you for telling me.

a-read the array from a horizontal arraySure! The array values are in A1:F1:

=LET(y, F2, x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=F3), A1:F1, ""))

b-ignore the blanks cells, don't write them down. c-combine all cells into one like a-b-c-d, a-b-c-e and etc=LET(y, F2, x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), TEXTJOIN("-", TRUE,IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=F3), A1:F1, "")))

thanks oscar!

a-working good!

b-i still need it, because it'll save me time, i want to repeat the function, and i rather not write down the range each time, but to give it a fixed range, like a1:q1 and then "fill right"

c-wasn't working for me

this is what i used:

=LET(y, COUNTA(T2:AJ2), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), TEXTJOIN("-", TRUE,IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=6), t2:aj2, "")))

and with what you wrote it gave me all in one line, and i need it to spill each combination to different cell vertically

1-2-3-4

1-2-4-5

1-2-4-6

and etc

is it possible?