Excel formulas in Excel is able to help you finish some rather complex tasks. But you will certainly meet with formula errors in Excel. In this article, we will show you the 8 formula errors in Excel.
There are many reasons that will cause formula errors in Excel. For example, you are not familiar with the rule of a certain function, or there is something wrong with the referred cells. In the following parts, we will demonstrate the 8 formula errors with some examples.
1. #VALUE!
This error will occur mainly because the formula contains many different types of values. The Image below shows such an example. Cell A2 contains a text value, while cell B2 contains a number. Therefore, you will see the “#VALUE!” error in cell.
Besides, except for this reason, this error will also occur in other conditions. There is a formula of the OFFSET function in this cell. But this formula will contain two cells. As a result, you will also see this error in the worksheet.
2. #NAME?
This error means that you may use the wrong function name in a formula. Suppose now you need to calculate the average value of a range. And you will certainly use the AVERAGE function. However, you type the “average” into the cell. And this will cause this error.
Excel will update and there are many different versions of this application. In newer version, there will be some newer functions. If you use newer functions in older versions of Excel, you will also see this error. For example, if you use the AVERAGEIFS in Excel 2003, you will see the “#NAME?” error.
3. #DIV/0 ！
This error is very easy to understand. When a number is divided by zero or an empty value in a formula, Excel will display this error. In the image below, you can see the formula in the formula bar. There is no value in B3. Thus, you will only see this error.
Therefore, when you need to change the values in a worksheet, you need to check other related cells.
4. #N/A
According to the name, you will know that this means not available. When a cell is not available in a formula, you will see this result. In this image below, you can see the VLOOKUP function in a range. But here we use the “TRUE” for the last argument.
When the formula cannot find the value, it will return this error. Therefore, when you use a certain function, you need to make sure that all the arguments are correct.
5. #####
This error will happen when the cell width is not larger enough to show all the characters. The number in cell A1 is 1231068, while the width of this cell is very small. Therefore, Excel will show “###” in the cell.
Besides, when a cell contains negative dates, you will also see this error. Here the cell format is “Date”. And there is a minus sigh in the cell. Thus, it will show this error.
6. #NULL!
This error means there is wrong regional operator in a formula. Here we will also show you an example. Here we use the SUM function to sum up the two ranges in the worksheet. However, we forget the comma between different arguments, you will see this error.
And in another cell, we have added the comma, and this formula will return the right answer. When you use a certain formula in your worksheet, don’t forget such elements.
7. #NUM!
This error means that there contains invalid values. In this image below, we have used an example. This formula is “=2^12345”. The result is infinity. Thus, Excel will return “#NUM!” in the cell.
You can see that this error can be avoided easily.
8. #REF!
This is the last error. When the cell reference is invalid, the formula will return this error. This may happen when you delete cells in the referred area. Here we will use this example. Now, the formula will return the right number in the cell.
Next delete the row3 in the worksheet. And you will find that the result will change into an error. This is because the referred cell has been deleted from the worksheet. And this formula will not update accordingly.
The next time you need to delete cells, you need to check if there is formula that will use the target cells.
From the above analysis, you will be clearly about those 8 formula errors in Excel. Whenever you meet with them, you will know how to deal with those errors.
Excel Errors can be Rather Complex
Except for the formula errors, Excel itself will also produce errors. What’s more, those errors can be rather complex. Therefore, you need to use a specialized Excel recovery tool to repair corrupted Excel xlsx file. This tool can handle most of the problems in Excel. Besides, it can get back your precious data and information from damaged files easily.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupted doc document and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply