Excel ISERROR Function
Summary
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when found.
Purpose
Test for any error
Return value
A logical value (TRUE or FALSE)
Syntax
=ISERROR (value)
Arguments
- value - The value to check for any error.
Usage notes
Use the ISERROR function to see if a cell contains any error message, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!
For example, =ISERROR(A1) will return TRUE if A1 is is displaying an one of the errors mentioned above , and FALSE if not.
Frequently, value is supplied as a cell address, but you can use it to trap errors inside more complex formulas as well.
ISERROR formula examples
To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel...
To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the generic form of the formula (above) rng represents the range in which you'd like to...
If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. From the example, the formula looks like this:
=LEFT(B4,FIND(" ",B4)-1)
How this formula...
The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas...
About the #DIV/0! error
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or...
The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "...
If you need to match the first error in a range of cells, you can use an array formula based on the MATCH and ISERROR functions. In the example shown, the formula is:
{=MATCH(TRUE,ISERROR(B4:B11),0)}
This is an array...
To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula:
=SUMPRODUCT(--ISERROR(B5:B9))
How this formula works...
The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a...
Related videos
Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use. In this video, we'll look at some ways to trap errors in a formula.
Related functions
