Hello Excellers and welcome back to another #Formula#Friday #ExcelTip in my 2019 series. Today, I want to share with you a really cool Excel Function. The COMBIN function. Have you ever needed to calculate all combinations for a specified number of items?. For example the number of two-person teams that could be generated from 10 people?. Or, how many combinations of two letters are possible from a total set of 5 letters. This is the example we will use today.

I have five letters, ABCD and E. This could represent all people available athletes in a competition. We want to work out how many combinations of teams of two we can get from this number of people. Let’s look at the function we use below.

The COMBIN Function.

Introduced into Excel in 2000 it is in the Math/Trig Functions category. The Syntax of the function is as follows:-

=COMBIN(number, chosen)

Where

number is the number of items

chosen is the number of items in a combination

 

Finally, A few points to note on the COMBIN Function.

• COMBIN Function calculates the highest number of combinations available based upon a fixed number of items.
• The internal order of the items does not matter.  For example AB is the same as BA.

Writing The Formula.

Let’s look at writing the formula. We have 5 possible athletes. We want to have as many sets of 2 as possible. The formula is Total Objects as 5 and the object in a set as 2. This gives us a total number of combinations as 10.

Why dont’ we check the workings of this manually also.  This sometimes gives us a better understanding of the basics of the formula.  I have already set up a matrix to write the combinations manually.  This is a very small exampe so is easy to working through by hand for practice and understanding how the formula can calculate all combinations of teams of two.

 

Following the rules we know apply to this function we also have 10 combinations of teams of 2. Those being, the function calculating the total number of combinations and also the order is not significant. We can have AB representing the same a s BA. So we correctly reach the same result as Excel. Have you ever used this Excel function before?. Share in the comments below how you have used this.

What Next? Want More Excel Tips?

So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

A Full list Of Formula Friday Blog Posts

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.