We frequently need to carry out some challenging mathematical computations while using Excel. The majority of these intricate mathematical calculations can be handled by Excel’s excellent computing capabilities. One of them is calculating the factorial of large numbers. However, when computing the factorial of large numbers, even Excel has a limitation. In this article, we will learn two convenient methods to calculate the factorial of a large number in Excel. After learning these two methods, we will not only be able to calculate the factorial of a large number but also be able to overcome the limitation. So, let’s start with this article and explore these methods.
How to Calculate Factorial of Large Number in Excel: 2 Convenient Ways
In this section of the article, we will learn two convenient methods to calculate the factorial of a large number in Excel. Let’s say we have the following Numbers as our dataset. Our goal is to calculate the factorial of these numbers.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Calculating Factorial of Number Less Than 171
In the first method, we will learn to calculate the factorial of numbers that are less than 171. You may ask, “What is so special about this 171?” Let’s clarify. In Excel, the maximum possible number that we can use is 9.99999999999999E+307. The factorial of 171 is 1.24101807 E+309, which exceeds the limit of Excel. So, if we want to calculate the factorial of 171 or any higher number, Excel will return a #NUM! Error. We will see this later in this section. Now, let’s follow the steps mentioned in the following section.
Steps:
- First, use the following formula in cell C5.
=FACT(B5)Here, cell C5 represents the first cell of the Number column. Then the FACT function will return the factorial of 100 in cell C5.
- After that, press ENTER.
As a result, you will have the following output on your worksheet.
- Finally, use the AutoFill option of Excel to get the remaining outputs as demonstrated in the following image.
Note: As we discussed earlier, you can see that factorial 171 returns a #NUM! Error.
Read More: How to Do Factorials in Excel
2. Computing Factorial of Number Greater Than or Equal to 171
So, can we calculate the factorials of numbers that are larger or equal to 171 in Excel? We can definitely do it. To do this, we need to create a user-defined function using the VBA Macro feature of Excel. In the following section, we will learn the detailed steps to do it.
Steps:
- Firstly, go to the Developer tab from Ribbon.
- Following that, choose the Visual Basic option from the Code group.
Subsequently, the Microsoft Visual Basic window will open on your worksheet.
- After that, go to the Insert tab from the Microsoft Visual Basic window.
- Then, choose the Module option from the drop-down.
- Now, write the following code in the newly created Module.
Function large_factorial(number_1 As Integer) As String
Dim i As Integer
large_factorial = "1"
For i = 2 To number_1
large_factorial = str_mult_f(large_factorial, CStr(i))
Next i
End Function
Function StrAdd(string_1 As String, string_2 As String) As String
Dim remaining As Integer
Dim sum_value, interim_value As String
remaining = 0
count_value = 1
For i = Application.Max(Len(string_1), Len(string_2)) To 1 Step -1
If Len(string_1) >= count_value Then
Str1Add = Val(Mid(string_1, Len(string_1) - (count_value - 1), 1))
End If
If Len(string_2) >= count_value Then
Str2Add = Val(Mid(string_2, Len(string_2) - (count_value - 1), 1))
End If
sum_value = Str1Add + Str2Add + remaining
Str1Add = 0
Str2Add = 0
count_value = count_value + 1
If sum_value > 9 Then
remaining = 1
sum_value = sum_value - 10
Else: remaining = 0
End If
StrAdd = Trim(Str(sum_value) & StrAdd)
Next i
If remaining Then StrAdd = Str(remaining) & StrAdd
StrAdd = Trim(StrAdd)
End Function
Function str_mult_f(number_1 As String, number_2 As String) As String
Dim multi_1(), multi_2(), multi_3(), answer As String
Dim chunk_value As Integer
Dim step_number As Integer
number_1 = Trim(number_1)
number_2 = Trim(number_2)
chunk_value = 7
step_number = 1
If Len(number_1) / chunk_value <> Int(Len(number_1) / chunk_value) Then
number_1 = String(chunk_value - (Len(number_1) - _
(Int(Len(number_1) / chunk_value) * chunk_value)), "0") & number_1
End If
If Len(number_2) / chunk_value <> Int(Len(number_2) / chunk_value) Then
number_2 = String(chunk_value - (Len(number_2) - _
(Int(Len(number_2) / chunk_value) * chunk_value)), "0") & number_2
End If
ReDim multi_1(Len(number_1) / chunk_value)
ReDim multi_2(Len(number_2) / chunk_value)
For i = Len(number_1) To 1 Step -chunk_value
multi_1(step_number - 1) = Trim(Mid(number_1, Len(number_1) - _
(step_number * chunk_value - 1), chunk_value))
step_number = step_number + 1
Next i
step_number = 1
For i = Len(number_2) To 1 Step -chunk_value
multi_2(step_number - 1) = Trim(Mid(number_2, Len(number_2) - _
(step_number * chunk_value - 1), chunk_value))
step_number = step_number + 1
Next i
step_number = 0
ReDim multi_3(UBound(multi_1()) * UBound(multi_2()))
For i = 1 To UBound(multi_1())
For j = 1 To UBound(multi_2())
multi_3(step_number) = multi_1(i - 1) * multi_2(j - 1) & _
String(((i - 1) + (j - 1)) * chunk_value, "0")
step_number = step_number + 1
Next j
Next i
answer = "0"
For i = 0 To UBound(multi_3()) - 1
answer = StrAdd(answer, (multi_3(i)))
Next i
str_mult_f = answer
End Function
- After writing the code, click on the Save icon as marked in the following picture.
- Next, press the keyboard shortcut ALT + F11 to open the worksheet from the VBA editor.
- After that, use the following formula in cell C5.
=large_factorial(B5)Here, cell B5 indicates the first cell of the Number column.
- Then, press ENTER.
Subsequently, you will get the factorial of 200 in cell C5 as shown in the following image.
- Lastly, apply the AutoFill feature of Excel to get the remaining outputs.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to calculate the factorial of a large number in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.
