A to Z of Excel Functions: The ISERROR Function
10 May 2021
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the ISERROR function.
The ISERROR function
At the time of writing, there are 12 IS functions, i.e. functions that give rise to a TRUE or FALSE value depending upon whether a certain condition is met:
- ISBLANK(reference): checks whether the reference is to an empty cell
- ISERR(value): checks whether the value is an error (e.g. #REF!, #DIV/0!, #NULL!). This check specifically excludes #N/A
- ISERROR(value): checks whether the value is an error (e.g. #REF!, #DIV/0!, #NULL!). This is probably the most commonly used of these functions in financial modelling
- ISEVEN(number): checks to see if the number is even
- ISFORMULA(reference): checks to see whether the reference is to a cell containing a formula
- ISLOGICAL(value): checks to see whether the value is a logical (TRUE or FALSE) value
- ISNA(value): checks to see whether the value is #N/A. This gives us the rather crude identity ISERR + ISNA = ISERROR
- ISNONTEXT(value): checks whether the value is not text (N.B. blank cells are not text)
- ISNUMBER(value): checks whether the value is a number
- ISODD(number): checks to see if the number is odd. Personally, I find the number 46 very odd, but Excel doesn’t
- ISREF(value): checks whether the value is a reference
- ISTEXT(value): checks whether the value is text.
As stated above, the ISERROR function checks whether the value is an error and returns either TRUE or FALSE accordingly. This specifically checks for all types of prima facie errors (e.g. #VALUE!, #REF!, #NAME? and the relatively new ones, #CALC!, #SPILL! and #FIELD!) including #N/A. It has the following syntax:
The ISERROR function has the following argument:
- value: this is required and represents the value for which you wish to determine whether it is an error.
It should be further noted that:
- technically, #N/A is not an error: it is a special value that you may manually enter into a cell to indicate that the necessary value is not available yet. If you wish to exclude this, use ISERR instead
- ##### is also not technically an error: this denotes that the column is not wide enough to display all the characters required in the current cell width
- The #CALC! and #SPILL! errors are only recognised in versions of Excel that support dynamic arrays; otherwise, these are treated as text strings and therefore are not considered errors
- IFERROR and IF(ISERROR) are different; IFERROR calculates the expression if there is no error; you may choose to calculate a different expression if there is no error with IF(ISERROR), e.g. IFERROR(5/2,0) = 2.5 but IF(ISERROR(5/2),0,1) = 1.
Please see my example below:
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.
A full page of the function articles can be found here.