Buzz
Previously, Mytour introduced readers to some common Excel errors. In this article, we provide a comprehensive guide to formula errors when using this calculation software. Below is a compiled list of Excel formula errors. Readers can refer to it for more information on fixing each type of error.
Compilation of Excel Formula Errors across Versions 2016, 2013, 2010, 2007, 2003
#NULL! - Occurs when two functions do not intersect.
#DIV/0! - Occurs when a formula attempts to divide by zero.
#VALUE! - Occurs if one of the variables in the formula is of the wrong type (e.g., a text value is incorrect when a numeric value is expected).
#REF! - Occurs when a formula contains an invalid cell reference.
#NAME? - Occurs when Excel fails to recognize a formula name or text within a formula.
#NUM! - Occurs when Excel encounters an invalid value.
#N/A - Indicates a value not available in the formula.
Excel Error #NULL!
Excel displays the #NULL! error when two functions do not intersect. For instance, the formula =SUM(B1:B10 A5:D7) returns the sum of values in the range B5:B7 (intersection of ranges B1:B10 and A5:D7).
However, if you input the formula =SUM(B1:B10 C5:D7), you will receive the #NULL! error message because the values in the range B1:B10 and C5:D7 do not intersect.
This error can be fixed by reviewing your formula and changing the variables to ensure valid intersections of functions, or by using the Excel Iferror function as a replacement, such as:
=IFERROR( SUM(B1:B10 C5:D7), 0 )
Error Excel #DIV/0!
Error Excel #DIV/0! occurs when a formula attempts to divide by 0. Clearly, dividing by 0 results in infinity, which cannot be a spreadsheet value, so Excel returns the error Excel #DIV/0!
For example, if cell C1 contains the value 0, then the formula: =B1/C1 will return the error #DIV/0!.
This error can be remedied by using the IF function to determine the result of division by 0, in which case it will produce a substitute result. For example:
Excel Error #REF!
When an Excel cell displays the Excel formula error #REF!, it indicates an invalid cell reference. There are two common scenarios that cause this Excel formula error:
1. The formula previously referenced a cell that has been deleted.
Example: Cell A1 contains the formula:
=B1+C1
If you delete column C in the spreadsheet now, the reference formula to C1 becomes invalid, and you'll receive the error message #REF!.
2. The formula has been copied from a reference cell located adjacent to the spreadsheet. When the formula is copied to a new cell, the range changes, and the range of results (assumptions) of reference cells outside the spreadsheet range.
For example, the formula displayed below is entered in cell A1, counting the number of cells in Sheet 2 containing the value 1:
=COUNTIF(Sheet2!1:1048576, 1)
If you now copy the above formula to cell C2, Excel will attempt to adjust the cell range to Sheet2!2:1048577. However, row number 1048577 does not exist (because there are only 1048576 rows in the current Excel sheet). Therefore, you'll receive the #REF! error.
In both examples above, if you click on the cell containing the #REF! error, you'll see the cell reference in the formula replaced with #REF!. So, to fix the error, you'll need to re-enter the correct cell references in the formula.
Note that in the second example above, you can avoid the error by changing the reference Sheet!1:1048576 to Sheet!$1:$1048576.
Excel Error #NAME?
When Excel encounters text within a formula, it will attempt to interpret the text as a reference, a range name, or a function name. If the text is not recognized, it will generate the #NAME? error.
For example, if you intend to enter the function =SUM(B1:C2), but mistakenly type =SM(B1:C2), Excel won't recognize the function name SM and produce the #NAME? error.
The approach to address and resolve the Excel function error #NAME? is to check if function names, references, and range names are correctly inputted, and to verify if any variables are enclosed in double quotation marks.
If your formula contains nested functions, check the result of each function individually until you identify the error.
Excel Error #NUM!
The Excel formula error #NUM! occurs when Excel detects an invalid value within the formula. For example, all square roots are positive numbers, so there's no square root of a negative number. Hence Excel's SQRT(-2) function will result in the #NUM! error, indicating an invalid argument.
To fix the #NUM! error, one way is to check each argument in your formula. Like with other argument types, if your formula is nested or composed of multiple parts, it's best to break down the formula and evaluate each argument separately.
Excel Error #N/A
The Excel formula error #N/A, or the Vlookup error, occurs when a value is not available in your formula. In the example below, you can see the use of the Vlookup function to find the value 'Cabbage' in column C on the spreadsheet and return the corresponding value in column D. However, because 'Cabbage' does not appear in column C, the VLOOKUP function cannot find this value and returns the #N/A error.
To identify and fix the error, you need to examine the values that the formula is accessing and determine why the requested value is not available.
If you believe the value you're trying to find is in the list you're searching, try accessing the Failure To Match Values page for further assistance in resolving this error.
Here, Mytour has just provided you with a comprehensive list of Excel formula errors and their fixes. If you have any doubts or questions that need answering, feel free to leave a comment below the article.
