The product of all positive integers less than or equal to n in mathematics is known as the factorial, indicated by the symbol (n!). In other words, the product of n and the subsequent smaller factorial also equal the factorial of n. So how will you calculate the factorial value of a number? Well, you can calculate those using your calculator, or you can do it by following some methods in Excel. Guess what today’s topic is? That’s right! How to do factorials in Excel.
Let’s assume we have a dataset, namely “Factorials of Consecutive Integer Numbers”. You can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Using FACT Function
To calculate the factorial value of a number, the FACT function is the first method that everyone wants to work with because of its workability, and all you need to do is put a simple command in the formula editor.
📌 Steps:
- To begin with this method, enter the following formula in cell B5.
= FACT(B5)Here, B5 refers to the Number column.
- Now see the output given below.
- Drag the Fill Handle tool to get the other value.
Read More: How to Calculate Factorial of Large Number in Excel
2. Applying FACTDOUBLE Function
Using the FACTDOUBLE function is another way to do factorials in Excel. To learn more about it, follow the discussion we are going to provide below.
📌 Steps:
- Write the following formula in cell C5.
=FACTDOUBLE(B5)*FACTDOUBLE(B5-1)The factorial is calculated by multiplying the two double factorials, which are obtained by multiplying the double factorial of the number in cell B5 and the double factorial of the number in cell B5 minus 1.
- Press Enter to see the output.
- To get the other value, drag the Fill Handle tool from C5 to B10.
3. Utilizing SEQUENCE Function
The SEQUENCE function allows you to have a list of sequential numbers in an array. As the factorial of a number means the product of the number with its subsequent lower values, we can incorporate the SEQUENCE function to calculate the factorial of a number. See the description given below on how to do factorials in Excel
📌 Steps:
- To begin with this method, enter the following formula in cell B5.
=PRODUCT(SEQUENCE(B5))Formula Breakdown
- SEQUENCE(3) → returns a sequence of numbers. Here, 3 is the rows
- Output → {1;2;3}
- PRODUCT(SEQUENCE(3)) → multiply those numbers and return its product value.
- Output → 6
- Hit the Enter button to see the output.
- Lower the cursor to the C10 cell to enable AutoFill.
4. Utilizing LAMBDA Function
You can add your own functions to the native Excel function library and make it unnecessary to copy and paste frequently used formulas by creating a function for them. The LAMBDA function can also be used by non-programmers because it doesn’t require JavaScript or VBA macros.
📌 Steps:
- First and foremost, click Formulas > Name Manager.
- This will open a prompt named New Manager.
- Press the New button.
- Add factorial as a name into the Name input.
- Paste the following formula into the Refers to box.
=LAMBDA(n,LET(integer,INT(n),IF(integer<2,1,integer*factorial(integer-1))))- Press the Close button afterward.
- Now type the following function in cell C5.
=factorial(B5)- Press Enter to see the output.
- To get the other value, drag the Fill Handle tool from C5 to C10.
5. Incorporating Power Query Feature
With the use of the Power Query feature, you can do factorial in Excel. Though you need to go through some extra steps to accomplish this, once you learn the process, there will be no burden that we can tell you.
📌 Steps:
- Go to the Data tab > From Table/Range command.
- Thus, a prompt will appear named Create Table and ask you to create a table. Select your data area, $B$4:$B10$ for instance.
- Press OK.
- Now, double-click on the region denoted by 1 and name the sheet where you want your output to appear.
- Click on the Number column if your data is not selected already.
- Then, from the Transform tab, navigate to Scientific > Factorial.
- Subsequently, your input value will be transformed into factorial value as described below.
- Now go to the Home tab and select the Close & Load option.
- Now, see the output given below.
6. Applying VBA Code
If you wish to create a customized function on your own and do factorials with it, then you can do this by incorporating a simple VBA code, which I am going to describe below.
📌 Steps:
- Press Alt + F11 to open your Microsoft Visual Basic.
- Then press Insert > Module to open a blank module.
- Now, write the following VBA code in your Module1.
Function FactorialUsingForLoop(val As Long) As Long
Dim MyInput As Long, MyFactorialValue As Long
Let MyFactorialValue = 1
For MyInput = 1 To val
MyFactorialValue = MyFactorialValue * MyInput
Next
FactorialUsingForLoop = MyFactorialValue
End Function
⚡ Code Breakdown
Now, we will explain how the given VBA code works. The code is divided into two steps.
- In the first portion, there are some variables that we need to use to run the code.
- In the second portion, it will run a For…Next statement from 1 to your given cell value, which is referred to by Val, and do multiplication each time to get a final factorial value.
- Close your VBA
- Now click on the C5 cell and type the following formula.
=FactorialUsingForLoop(B5)As you have already understood, the function we have written above was created by the given VBA code.
- Now press Enter to see the output.
- To get the other value, drag the Fill Handle tool from C5 to C10.
Things to Remember
- It is worth mentioning, that you can only calculate the factorial value of an integer number. Thus, all of the methods we have described above will be applicable only to integer numbers. You will not get any output otherwise.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
Conclusion
Throughout this article, we tried to show you 6 methods to do factorials in Excel. Further, if you have any queries, feel free to comment below.
