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.

- Select your Excel table data.
- Go to the
**Data**tab. - 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.

- Select your column of numbers to transform with the factorial operation.
- Go to the
**Transform**tab of the Power Query editor. - Click on the
**Scientific**command found in the**Number Column**section. - 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.

- Go to the
**Home**tab of the Power Query editor. - 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.

- Go to the
**Formulas**tab. - Click on the
**Name Manager**command.

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

- Press the
**New**button.

- Add
**FACTORIAL**into the**Name**input.

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

- Paste the above formula into the
**Refers to**input. - Press the
**OK**button to close the**New Name**menu. - 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!

## 0 Comments