Do you need to calculate the factorial of a number in Excel?

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.

= FACT ( 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.

= FACT ( B3 )

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)!!.

= FACTDOUBLE ( B3 ) * FACTDOUBLE ( B3 - 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.

= PRODUCT ( SEQUENCE ( B3 ) )

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.

1. Select your Excel table data.
2. Go to the Data tab.
3. Click on the From Table/Range command.

This will open the Power Query editor where you will be able to add the factorial calculation to your data.

4. Select your column of numbers to transform with the factorial operation.
5. Go to the Transform tab of the Power Query editor.
6. Click on the Scientific command found in the Number Column section.
7. Select Factorial from the options.

= Table.TransformColumns(#"Changed Type",{{"Number", Number.Factorial, Int64.Type}})

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.

8. Go to the Home tab of the Power Query editor.
9. Click on Close and Load.

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.

1. Go to the Formulas tab.
2. Click on the Name Manager command.

This will open the Name Manager which lists all your named objects in the Excel workbook.

3. Press the New button.

4. Add FACTORIAL into the Name input.

=LAMBDA(n,LET(integer,INT(n),IF(integer<2,1,integer*FACTORIAL(integer-1))))

5. Paste the above formula into the Refers to input.
6. Press the OK button to close the New Name menu.
7. Press the Close button to close the Name Manager menu.

📝 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.

=LAMBDA(n,IF(INT(n)<2,1,INT(n)*FACTORIAL(INT(n)-1))))

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.

= FACTORIAL ( B3 ) 

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.

Public Function Factorial(ByVal myNumber As Integer) As Long
If myNumber < 1 Then
    Factorial = 1
Else
    Factorial = myNumber * Factorial(myNumber - 1)
End If
End Function

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.

function factorial(myNumber: number): number {
    if (myNumber == 0) {
        return 1
    }
    else {
        return myNumber * factorial(myNumber - 1)
    }
};

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!