Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

How to use factorial (!) in Excel

What is a factorial?

A factorial calculates the ‘product’ of all numbers less than or equal to a value. For example, the factorial of 5 would be:

5x4x3x2x1=120

In mathematical notation, factorials are usually indicated with an exclamation mark. 5! would indicate the factorial of 5.

Factorials are typically used to calculate a number of possible combinations (or permutations). For example, the British National Lottery sells lottery tickets that contains 6 numbers from 1 to 59. The mathematical formula to calculate the total possible combinations of numbers is:

59!/(6!*(59-6)!)

This results in a value of 45,057,474, showing that a single ticket has a 1 in 45,057,474 chance of winning the British National Lottery!

Factorials in Excel

Because the exclamation mark is the symbol for a factorial you might expect it to be recognized by Excel, but you will get an error message if you try to enter a formula such as =5!.

To calculate factorials in excel you must use the FACT function.

=FACT(5) would calculate the factorial of 5 in Excel.

If you’re unfamiliar with Excel formulas and functions you could benefit greatly from our completely free Basic Skills E-book.

Many more advanced functions are explained in depth in our Expert Skills Books and E-books.

Calculating the probability of winning the lottery in Excel

Now that you know how to use the FACT function, it’s a simple matter to translate the mathematical formula shown above into an Excel formula:

=FACT(59)/(FACT(6)*FACT(59-6))

You can download an example workbook showing this in action.

Other combinatorial functions in Excel

Excel also contains a number of other useful ‘combinatorial’ functions. One of the most useful is COMBIN. COMBIN makes the calculation shown above even easier, as it allows you to return the total number of possible combinations from a given number of items. You can calculate the probability of winning the lottery with the simple formula:

=COMBIN(59,6)

Excel also offers the COMBINA function, which works the same way but allowing repetition of numbers. If you were allowed to choose the same number more than once in the lottery there would be 74,974,368 possible combinations and it would be much harder to win!

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

Excel protected view warning

Excel protected view: Be careful – files from the Internet can contain viruses. Unless you need to edit it’s safer to stay in Protected View. This article explains what this error message means.

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Clients

When you use The Smart Method you’re in good company Here are some of the companies that The Smart Method have helped empower with excellent

Microsoft Office update channels

Microsoft Office update channels are explained in this article. Beta, Current, Monthly Enterprise and Semi-annual enterprise are all covered.

2 Responses

  1. Hi Pete

    The factorial of 4 is indeed 24:

    4*3*2*1 = 24

    … and if you use the formula =FACT(4) in Excel it will correctly return 24.

Leave a Reply

Your email address will not be published.

18 − eleven =