Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Calculating Combinations.

Calculating Combinations

by Allen Wyatt
(last updated May 14, 2016)

4

Have you ever wondered how many different four-character combinations you can make when you start with 26 letters and 10 digits? Excel can tell you in a flash, if you use the COMBIN worksheet function. The syntax of the function is as follows:

=COMBIN(universe, sets)

In this syntax, universe represents the number of items from which the combinations can be made, and sets represents the number that must be in each combination. Thus, you could use the following to determine the number of four-character combinations that you can make from the alphabet (26 letters) and the ten digits:

=COMBIN(26+10,4)

Just for those who are curious, the answer returned is 58,905.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12202) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Calculating Combinations.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time ...

Discover More

Understanding AutoComplete

Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...

Discover More

Resizing an Image in a Document

When you first insert an image in a document, Docs tries to guess the best size for that image. Chances are good that ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Using the ABS Function

Need to find the absolute value of a number? That's where the ABS function comes into play.

Discover More

Making PROPER Skip Certain Words

The PROPER worksheet function is used to change the case of text so that only the first letter of each word is uppercase. ...

Discover More

Returning Blanks with VLOOKUP

Normally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2017-06-17 15:31:27

Peter

Dennis

I don't know if there is an Excel function for this case (other than using my formula and the FACT function).

Try the PERMUT function


2017-06-14 11:43:53

Dennis Costello

To be completely pedantic about it, there are several possibilities here:
- 4 character places, each of which can be any member of the set (i.e., duplicates allowed): the "license plate" use case. This is what Tony said, and his value is 36^4 (36 to the 4th power)

- 4 characters pulled from the set with no duplicates allowed and insensitive to the order: this is the "hand" case when dealing a hand from a deck of cards. As David pointed out, this is what Combin does, and it's equal to n! / ( (n-m)! * m! ), where n is 36 in this case and m is 4. This is n-factorial (the product all all integers 1, 2, 3, ..., n) divided by the factorial of n-m and then again by m-factorial. After the first division the result is 36 * 35 * 34 * 33 - the second division is by 4 * 3 * 2 * 1

- 4 characters pulled from the set, no duplicates, but the order matters: this would be n! / (n-m)! and there are 1,413,720 such possibilities. I don't know if there is an Excel function for this case (other than using my formula and the FACT function).


2016-05-19 17:48:17

Tony

As David stated the answer is for the single combination that he explained and only using a letter or number once. If you can use any combination and repeat letters and numbers it would be 1,679,616.


2016-05-14 11:46:48

David

It's worth noting that COMBIN gives the number of combinations possible *in any order*. In other words, it considers "abcd", "acbd", "dcba" etc. all as a single combination.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.