Excel is just a really big and flexible calculator, so it makes sense that some people use it for math problems. The factorial comes up a lot in statistics when calculating permutations and combinations of events. So it’s likely one you’ll need to calculate quite a bit when doing any statistical work in Excel. This post will show you all the ways you can find the factorial of an integer number in Excel.
What is a Factorial?
The factorial of an integer number is the product of all integers up to and including that number. The factorial of a number n is usually represented with an exclamation character n! after the number. Here are a few examples of factorial calculations.
5 factorial is represented as 5! and this is just 5 * 4 * 3 * 2 * 1 = 120.6 factorial is represented as 6! and this is just 6 * 5 * 4 * 3 * 2 * 1 = 720.7 factorial is represented as 7! and this is just 7 * 6 * 5 * 4 * 3 * 2 * 1 = 5040.
As you can see the values quickly get large. ⚠️ Warning: You will only be able to calculate up to 170! in Excel, and after this, the calculation will return a #NUM! error because the result will be too large.
Calculate the Factorial with the FACT Function
Excel has a dedicated function for calculating the factorial of a number. The FACT function takes a positive integer value and returns the factorial for this number.
number is the number to return the factorial based on.
The above formula will return the factorial of the value in cell B3. The number passed to the FACT function needs to be a positive integer value.
Negative numbers will result in a #NUM! error.0 and 1 factorial will result in 1.Positive decimal values are truncated before the factorial is calculated.
Calculate the Factorial with the FACTDOUBLE Function
There is another factorial function in Excel called FACTDOUBLE. From the function name, you might think it takes the factorial and multiplies it by 2. But this is not what it does. The double factorial (or semifactorial) is another mathematical calculation that is defined in a similar way to the factorial.
For even numbers, the double factorial is the product of all even integers up to and including that number.For odd numbers, the double factorial is the product of all odd integers up to and including that number.
The double factorial of n is represented by two exclamation mark characters n!!. Here are a few examples of double factorial calculations.
The double factorial of 5 is represented as 5!! and this is just 5 * 3 * 1 = 15.The double factorial of 6 is represented as 6!! and this is just 6 * 4 * 2 = 48.The double factorial of 7 is represented as 7!! and this is just 7 * 5 * 3 * 1 = 105.
This means the factorial of n is the same as the double factorial of n multiplied by the double factorial of n-1. In symbols this is n! = n!! * (n-1)!!. The above formula will return the factorial of the value in cell B3. This formula calculates the double factorial of the number in cell B3 and the double factorial of the number in B3 minus 1, then multiplies the two double factorials which results in the factorial.
Calculate the Factorial with the SEQUENCE Function
While there is a dedicated function to calculate your factorial values, there are some other creative options available. You can use a combination of the PRODUCT and SEQUENCE functions to create a factorial calculation. The above formula will return the factorial of the number in cell B3. The SEQUENCE function allows you to create a dynamic array of values that starts at 1 and increments by 1 up to the given number in cell B3. These are exactly the values you need to calculate a factorial. The PRODUCT function can then be used to multiply each of those values from the SEQUENCE. This result is exactly the factorial of the number in cell B3.
Calculate the Factorial with Power Query
Power Query has many advanced mathematical functions available and this also includes a factorial function. You’ll be able to calculate the factorial for any column of numbers inside an Excel table using Power Query. This will open the Power Query editor where you will be able to add the factorial calculation to your data. This will apply something similar to the above M code formula as a step in your query and your column will now show the factorial values in the data preview. You can now load the data back into Excel. This will open the Import Data menu where you can select to import the data to a Table and then choose the location of the table.
Calculate the Factorial with the LAMBDA Function
The factorial calculation is recursive. This means you can define the current value based on the previous value. The factorial of n is actually n multiplied by the factorial of n-1. In symbols this is n! = n * (n-1)!. There is one function in Excel that can handle recursive calculations. This is the LAMBDA function. The LAMBDA function allows you to build your own custom functions. You can use this to build your own version of a factorial function. To use the LAMBDA function recursively, you will first need to define your custom function with the name manager. This will open the Name Manager which lists all your named objects in the Excel workbook. 📝 Note: The Name used in the New Name menu must match what’s used in the Refers to input of the LAMBDA formula in order to recursively reference the calculations. The integer*FACTORIAL(integer-1) is the main part of the formula calculation which recursively calculates the factorial. The IF function tests the number first and applies the recursive calculation if it’s a number bigger than 1, otherwise, the factorial result will be 1. The LET function just helps to simplify the formula by defining integer as INT(n) which is the integer part of the number n. This is later used in multiple places in the factorial calculation. Alternatively, without the LET function, the LAMBDA formula could be written as above. The LET function will save the INT function from being used multiple times.
Now the new FACTORIAL function is defined in the Name Manager and it can be used just like any other function anywhere in the workbook. The above custom LAMBDA function will return the factorial based on the value in cell B3.
Calculate the Factorial with VBA
VBA has a lot of built-in functions but there is no factorial function. But you can create a function that can be called in any other procedure to calculate the factorial if needed. The above code creates a factorial function that takes a number as its argument and returns the factorial. This code is a recursive definition as you can see the Factorial() function calls itself to perform the calculation.
Calculate the Factorial with Office Scripts
The TypeScript based language found in Office Scripts also does not have a built-in factorial function available. But you can also build your own factorial calculation in a recursive manner which can then be called from your main function. The above Office Script function will return the factorial of a given number. The function is defined recursively. You can see that factorial() calls itself in the calculation.
Conclusions
There are many reasons why you might need to calculate the factorial of a number in Excel. This post has shown you the different methods you can use. The most straightforward way to get the factorial will be with the FACT function. But alternatively, you can get the same result with a few other creative formulas involving the FACTDOUBLE, SEQUENCE, and PRODUCT functions. Power Query also offers a factorial M code formula that is easily used from the editor ribbon. Since the factorial can be recursively defined, you can even create your own custom function using the LAMBDA function’s recursive powers. A similar recursion logic can be used to build VBA and Office Scripts functions to get the factorial as well. Did you know all these ways to get the factorial in Excel? Let me know in the comments!
