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.
Download Practice Workbook
2 Convenient Ways to Calculate Factorial of Large Number in Excel
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.
- Firstly, use the following formula in cell C5.
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.
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.
- 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.
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.
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.