This article is a full guide about the #NUM error in MS Excel. We will tell you what's the meaning of this error and how to correct it. For further use, you will also learn how to avoid the #NUM error in this post.
When you received an Excel document and want to check its content, but only to find that there are some #NUM errors, what you want to do? Of course, you are wondering why this error happens and want to correct it immediately. This is also what we will introduce in this article, telling you why you would see this #NUM error in Excel and how to correct it. Keep reading and check more details.
The #NUM error can appear for many reasons, generally, there are three common reasons.
One of the main reasons why you see the #NUM error is that the number is either too big or too small. Excel has limits on the smallest and largest numbers users can use. If the number you enter is out of this range, you will see it shows as #NUM. For your information, the smallest available number in Excel is -1*10^308, which means a -1 with 308 zeros after it. The largest available number is 1*10^308, which means a 1 with 308 zeros after it.
Another possible reason is you might perform an impossible calculation, and Excel can only display a #NUM error. For example, when you perform a wrong formula, you will get a #NUM. The below image shows when users want to take the square root of a negative number in Excel, it shows the #NUM error.
An iterative formula is a formula that performs the same calculation repeatedly. An example of this is the IRR function, which calculates the internal rate of return for a series of cash flows. IRR performs calculations cyclically until the result is accurate to within 0.00001%. If IRR cannot find a valid result after 20 attempts, Excel will display a #NUM error.
There are different solutions for #NUM errors caused by different causes. In this part, we will tell you how to do it one by one.
If you see the error because the number is out of the Excel limitation range or you perform an impossible calculation. Then the only solution is to check your number and formulas and change them to the right one.
If you perform a formula like IRR or RATE, and you see the #NUM error, you can apply the following steps to change the number of times Excel iterates formulas.
Step 1. Open MS Excel, go to File > Options.
Step 2. Click the Formulas tab, and check the Enable iterative calculation box under Calculation options.
Step 3. Type a large number in the Maximum Iteration box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
Step 4. Enter the amount of change you can accept between calculation results in the Maximum Change box. You should keep the number as small as possible.
How to Fix Runtime Error 1004 in Excel?
How to Fix Runtime Error 1004 in Excel? There are four effective methods for you to choose from.
First check if the number exceeds the Excel limitation, then check if you apply the correct formula. At last, go to the Excel file option to change the iterative calculation settings.
If you have deleted the cells referenced by formulas, your excel will show #REF! error because the cell's data is invalid.
To fix the error: Press Ctrl + Z to undo the delete operation. If the Ctrl + Z works, this error will be corrected.
To delete the error: Press Ctrl +F to find #REF! error, and type blank to replace it.
Files can be lost or deleted in many situations. For example, when you working with an Excel document, a sudden computer shut down could let you lost your file. Besides, accidentally format a hard drive or virus attack will also lead to data loss.
This is why we want to recommend EaseUS Data Recovery Wizard here, no matter how you lost your files, this data recovery software will find and recover the files for you.
Here is the example of how EaseUS Data Recovery Wizard recovers the Excel document, you can check the whole process and download the tool to have a free trial.
Step 1. Select location and click "Scan"
Choose the drive or a specific folder on Desktop or somewhere else where you lost the excel files, and click the "Scan" button.
Step 2. Locate your excel files
To quickly find the Excel files from the scan results, click the "Documents" option in the left panel. Choose "Excel", and click "Preview" to check if the Excel files are the wanted ones.
Step 3. Recover Excel files
Finally, select the desired Excel files and click the "Recover" button. Then, select a folder to save the Excel files to another location.
Related Articles
Fix Page_Fault_In_Nonpaged_Area Error
How to Fix: SD Card Command Volume Partition Disk Failed with Null
6 Fixes for Excel File Keeps Closing Automatically | Excel File Recovery Tips
4 Methods | Convert RAW to FAT32 for Micro SD Card/USB