How to Calculate Factorials in Excel: A Complete Guide

The concept of factorials occurs in many branches of mathematics and finds application in solving a vast range of practical problems.

In this tutorial, we'll learn what the factorial of a number is, what it is used for, how to calculate factorials in Excel, what limitations and possible issues we should keep in mind when calculating factorials in Excel, some specific variations of factorials that can we can compute in Excel, and a few Excel functions that involve factorial calculation. Along the way, we'll consider some examples of factorial application.

To learn or refresh your knowledge of Excel, you can find the Introduction to Excel course and Excel Formulas Cheat Sheet helpful.

What Are Factorials, And What Are They Used For?

The factorial of a positive whole number is the product of all the whole numbers from 1 to that number inclusive. The mathematical notation for a factorial is the exclamation mark put right after the number for which we need to calculate the factorial.

For example, to calculate 7!, read as "seven factorial," we need to multiply all the whole numbers from 1 to 7 inclusive, as follows: 7! = 1*2*3*4*5*6*7. The result of the above expression is 5040.

In simple words, the meaning of the factorial of a number n is the number of ways we can arrange a group of n distinct objects, considering that we have to use all n objects for each arrangement and no specific criteria are imposed for such arrangements.

Returning to the above example of calculating 7!, we can use this formula to find out in how many ways we can put 7 different books in a row on a bookshelf.

Being the backbone of calculating combinations and permutations, factorials are widely used in mathematics (including in algebra, probability theory, combinatorics, number theory, game theory, etc.), statistics, computer science, artificial intelligence, engineering, natural sciences, economics, finance, and business.

How to Calculate a Factorial In Excel

If we try to calculate the factorial of 7 by entering =7! into an Excel cell, the software will throw a warning. This is because Excel can't recognize the mathematical representation of factorial as a valid operation.

Instead, to calculate the factorial of a number n in Excel, we can use other approaches: from a direct multiplication of all the whole numbers from 1 to n inclusive (1*2*3*...*n) to applying a combination of Excel functions (e.g., PRODUCT(SEQUENCE(n))).

However, the most straightforward way of calculating the factorial of a number in Excel is by using the dedicated FACT function, which is available for all Excel versions starting from Excel 2000.

FACT Excel Function

Syntax

The syntax of the FACT function is very simple since it has the only parameter (required):

FACT(number),

where number is a non-negative number that we want the factorial of.

The function returns the factorial of a number.

Note that we can pass to the function either the number itself (e.g., FACT(7)) or the cell address of the cell where the number of interest is stored (e.g., FACT(A1)).

The FACT function can be used as it is or within a formula, in combination with other functions and mathematical operations. For example, FACT(10)/(FACT(2)* FACT(8)).

To explore other common functions used in Excel, you can refer to the tutorial on The 15 Basic Excel Formulas Everyone Needs to Know.

Function usage

We can use the FACT function in Excel in one of the following three ways:

1. Click on the cell where we want to display the result of the function, enter an equal sign, start writing the function name, and select it from the dropdown list:

Next, insert either the number itself that we want the factorial of or the cell address of the cell where that number is stored, close the parentheses, and press Enter.

2. Click on the cell where we want to display the result of the function, click on the formula bar, enter an equal sign, start writing the function name, and select it from the dropdown list:

Next, insert either the number itself that we want the factorial of or the cell address of the cell where that number is stored, close the parentheses, and press Enter.

3. Click on the cell where we want to display the result of the function, open the Formulas tab, select the Maths & Trig category under the Function Library group, scroll down to the FACT function, and click on it:

In the pop-up window Function Arguments, we can insert one by one various numbers that we want the factorials of or cell addresses of the cells where such numbers are stored, and immediately see the result for each value before pressing OK, which would display the result of the FACT function for the last provided value.

Also, this window reminds us of the definition of factorial, the function syntax, and where to find the function documentation:

Special cases

1. FACT(0) = 1
2. FACT(1) = 1
3. Applying the FACT function on a decimal number: the number will be truncated under the hood, and the factorial of its integer part only will be returned.

Possible errors

• #VALUE!—a non-numeric value was passed to the function.
• #NUM!—the value passed to the function is either negative or greater than or equal to 171.

Limitations

In the previous section, we mentioned that the FACT function throws the #NUM! error if we pass to it a number greater than or equal to 171. This happens due to one of the intrinsic limitations of Excel: there is the largest positive number that Excel can calculate, and this number gets exceeded when we try to calculate FACT(171). Hence, the largest factorial that we can calculate in Excel without getting the #NUM! error is the factorial of 170 (equal to 7.257415615308E+306).

Moreover, Excel can't calculate such large factorials accurately because of its number precision limitations.

Considering that factorials grow very quickly, resulting in very large outputs for relatively small input numbers, the largest number for which Excel can accurately calculate the factorial (even though it will not display the result accurately due to its rounding limitations) is 22.

To overcome or mitigate these limitations of Excel, in the case we need to calculate the factorial of a large number, we can resort to various workarounds, such as using Excel VBA (Visual Basic for Applications, a programming language within Excel) or a dedicated Excel add-in.

However, if we need to work with such large numbers, it can make sense for us to opt for another software rather than Excel to perform our tasks more efficiently.

Examples

At the beginning of this tutorial, we've already mentioned an example of factorial application: to find out the number of ways in which we can put 7 different books in a row on a bookshelf, we need to calculate 7! in mathematical notation, which would be FACT(7) in Excel.

Let's consider two more examples.

1. Imagine that there are 10 books on a bookshelf (well, I'm a bookworm 😉), and we want to randomly pick 4 of them to read on holiday. How many possible combinations of 4 books out of 10 do we have? In mathematics, this is an example of combinations without repetitions. In Excel, we can use the FACT function for such calculations: FACT(10)/(FACT(4)* FACT(10-4)). The result of this calculation will be 210.

2. Imagine that we have a pile of 10 cards, on each of which we have written a word in a foreign language we learn. We want to refresh our knowledge by drawing a card and translating the word on it. After each drawing, we return the card to the pile, and we want to repeat this process 4 times, meaning that we can draw the same card more than once. How many possible combinations of 4 cards out of 10, considering eventual repetitions, do we have? In mathematics, this is an example of combinations with repetitions. In Excel, we can use the FACT function for such calculations: FACT(4+10-1)/(FACT(4) * FACT(10-1)). The result of this calculation will be 715.

We'll return to this and the previous examples when discussing Excel functions related to factorial, below.

If you’re looking to improve your understanding of the matters we’ve covered so far, the Data Analysis in Excel course will help you bring your Excel skills to a new level by learning advanced analytical techniques such as pivot tables, intermediate logical functions, what-if analysis, forecasting of future trends, and much more.

Excel Functions for Calculating Specific Types of Factorials

Apart from the regular factorial that we defined at the beginning of this tutorial, there are also some other types of factorials in mathematics: double factorial, primorial, superfactorial, etc. Those types of factorials are very specific, and they are usually used as a part of complex mathematical formulas. Let's briefly outline two Excel functions that enable a straightforward calculation of two of such types: FACTDOUBLE and GAMMA.

FACTDOUBLE Excel Function

The FACTDOUBLE function calculates the double factorial of a number, which is the product of all the whole positive numbers less than or equal to the input number and having the same parity as the input number. In mathematics, a double factorial is represented by two exclamation marks. For example, 8!! = 2 * 4 * 6 * 8 = 384 (in Excel, it would be FACTDOUBLE(8)).

The syntax of the FACTDOUBLE function is pretty similar to that of the FACT function:

FACTDOUBLE(number),

where number is a non-negative number that we want the double factorial of.

All the other things that we discussed earlier for the FACT function (function usage, special cases, possible errors, and limitations) are also identical, with the only exception for the largest value, for which Excel can calculate the double factorial.

For the regular factorial, this number is 170. For the double factorial, it's 300. Trying to calculate FACTDOUBLE(301) will result in the #NUM! error.

GAMMA Excel Function

This is an implementation of the mathematical gamma function, which extends the factorial function to complex numbers. In Excel, we can use the GAMMA function on negative decimal and positive decimal numbers.

The syntax of the GAMMA function is as follows:

GAMMA(number)

where number is a negative decimal or positive number that we want the gamma function value of.

Note that passing to the function a negative whole number or 0 will throw the #NUM! error.

Excel Functions Related to Factorial

Some Excel functions include the calculation of factorials inside more complex formulas. Let's take a quick look at those of them that allow us to compute the number of combinations and permutations.

COMBIN and COMBINA Excel Functions

Both COMBIN and COMBINA functions in Excel return the number of combinations, i.e., selections of a defined number of items from a set of items, when the selection order doesn't matter. The difference between these two functions is that COMBIN doesn't allow item repetitions, while COMBINA does.

The syntax of both functions is identical, so let's use the COMBIN function as an example:

COMBIN(number, number_chosen)

where

• number—the number of items in a set,
• number_chosen—the number of items in each combination.

Earlier, we talked about two examples of using the FACT function to calculate combinations. Returning to those examples, we can make our calculations much easier by applying the COMBIN and COMBINA functions.

1. Imagine that there are 10 books on a bookshelf, and we want to randomly pick 4 of them to read on holiday. How many possible combinations of 4 books out of 10 do we have? COMBIN(10, 4) = 210.
2. Imagine that we have a pile of 10 cards, on each of which we have written a word in a foreign language we learn. We want to refresh our knowledge by drawing a card and translating the word on it. After each drawing, we return the card to the pile, and we want to repeat this process 4 times, meaning that we can draw the same card more than once. How many possible combinations of 4 cards out of 10, considering eventual repetitions, do we have? COMBINA(10, 4) = 715.

PERMUT and PERMUTATIONA Excel Functions

Both PERMUT and PERMUTATIONA functions in Excel return the number of permutations, i.e., selections of a defined number of items from a set of items, when the selection order matters. The difference between these two functions is that PERMUT doesn't allow item repetitions, while PERMUTATIONA does.

The syntax of both functions is identical, so let's use the PERMUT function as an example:

PERMUT(number, number_chosen)

where

• number—the number of items in a set,
• number_chosen—the number of items in each permutation.

Conclusion

We’ve discussed the definition of the factorial of a number, the main fields of factorial application, the most straightforward way of calculating factorials in Excel by using the FACT function, what special cases, possible errors, and limitations of using this function we should be aware of, some specific types of factorials and how to compute them in Excel, and Excel functions related to factorial calculation. In addition, we looked at a few examples of calculations involving factorials and how to make them more efficient.

To demonstrate your proficiency in Excel to a potential or current employer and hence enhance your employability and career opportunities, consider earning a Microsoft Excel Certification. The guide on How to Earn a Microsoft Excel Certification in 2024: Top Tips and Resources will provide you with detailed step-by-step instructions, exam insights, and expert tips on how to be officially certified in Excel.