Author: Oscar Cronquist Article last updated on December 23, 2018

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations andÂ permutations.

Think of permutations asÂ if the order is important and combinations as if the order is not important.Â If this is confusing,Â look at the following examples.

### Example 1 - Permutations

Now imagine a phone number to a friend or co-worker. If we rearrange the phone numbers, you couldÂ possibly callÂ a stranger. The orderÂ is important.

Permutations with and without repetition

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]

### Example 2 - Combinations

Imagine you are about to buyÂ a pizza and you can choose from five ingredients, cheese, tomato sauce, onions, ham and mushrooms. It doesnÂ´t matter in what order you say the ingredients. The order is not important.

A five digit phone number has 100Â 000 possible permutations butÂ five out of five pizza ingredients has onlyÂ one combination.Â I guess onlyÂ mathematicians use the word permutations, everyone else use the wordÂ combinations even if they talk aboutÂ permutations.

The following article demonstrates a problem solved with combinations, order is not important:

Identify numbers in sum using Excel solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

### User defined functions

The following User Defined Function lets you, for example, seeÂ allÂ theÂ combinations of pizza ingredients you can choose from if you pick 5 out of 7Â ingredients.

Custom array formula in cell range E3:I23:

=Combinations(B3:B9,5)

You need to copy the VBA code below to your workbook before you try the above User Defined Function.

https://youtu.be/lZcKDfJ-HoE

How to enter an array formula

1. Select cell range E3:I23
2. Click in fomula bar
3. Enter custom function
4. Press and hold CTRL + SHIFT
5. Press Enter

If you did it right the formula now begins and ends withÂ curly brackets, like this {=array formula}. They appear automatically, donÂ´t enter the curly bracketsÂ yourself.

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

### VBA Code

```Public result() As Variant

Function Combinations(rng As Range, n As Single)
rng1 = rng.Value

ReDim result(n - 1, 0)

Call Recursive(rng1, n, 1, 0)

ReDim Preserve result(UBound(result, 1), UBound(result, 2) - 1)
Combinations = Application.Transpose(result)

End Function

Function Recursive(r As Variant, c As Single, d As Single, e As Single)
Dim f As Single

For f = d To UBound(r, 1)

result(e, UBound(result, 2)) = r(f, 1)

If e = (c - 1) Then

ReDim Preserve result(UBound(result, 1), UBound(result, 2) + 1)

For g = 0 To UBound(result, 1)
result(g, UBound(result, 2)) = result(g, UBound(result, 2) - 1)
Next g
Else
Call Recursive(r, c, f + 1, e + 1)
End If

Next f

End Function

```

These functions have a limit of 65532 rows, if you need more read this comment.

### Where to copy vba code?

1. Go to VB Editor (Alt + F11)
2. Click "Insert" on the menu
3. Click "Module"
4. Paste code to code module

Tip! Did you know that Excel can calculate the number of combinations for you? Use the COMBIN function:

=COMBIN(7,5) equals 21Â combinations.

Select numbers in each permutation

Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]

List permutations without repetition [UDF]

This blog post describes how to createÂ permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

List permutations with repetition [UDF]

This blog post describes how to create permutations. Repetition is allowed. Vba code: Function ListPermut(num As Integer) 'Permutations with repetition […]