The first function we want to take a look at is ‘ISERROR’.
The syntax is simple and only requires a single argument.
The ‘value’ is simply what we are checking for an error.
Most often, and for this tutorial, the ‘value’ argument is a cell reference.
Errors we are testing for
So what are the errors we are testing for using the ‘ISERROR’ function?
It tests for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.
- So if our ‘value’ argument is any of these errors, the ‘ISERROR’ function will return the result ‘TRUE’.
- Conversely, if the ‘value’ argument is not any of these errors, the function will return a ‘FALSE’ result.
This can be very useful when we would rather substitute another value in cells that would throw an error for some reason.
Let’s take a look at an example for this to make a bit more sense!
In this scenario, we will say that we have a data set consisting of a column of numerators and a column of denominators.
Then we have a column that contains the result of dividing the numerator by the denominator.
There are some zeros in the denominator column which will cause a divide by zero, or ‘#DIV/0!’, error for those rows in the ‘Result’ column.
If we use the ‘ISERROR’ function in our ‘Result’ column we will get either a ‘TRUE’ or ‘FALSE’ value indicating which rows do or do not contain an error.
Note: That the ‘#DIV/0!’ errors have now been replaced by ‘TRUE’.
We could go a step further and make Excel place a zero in those rows to replace the error for the sake of a cleaner look to our data while not interfering with the calculations on all other rows.
In order to do this, we could nest our ‘ISERROR’ function within and ‘IF’ function.
For anyone not familiar with the ‘IF’ function, it simply consists of a logical test (in this case, our ‘ISERROR’ function) and the value we choose for it to return for either the true or false result of that logical test.
‘=IF(logical_test, [value_if_true], [value_if_false])’
So where the formula in cell C2 is currently ‘=A2/B2’, we will now substitute a new formula:
What this formula actually says that if our ‘ISERROR’ function returns ‘TRUE’, return the value ‘0’ but if it is false, return the result of the original mathematical calculation.
Note that even though we have selected the number zero for our ‘value_if_true’ argument in the ‘IF’ function, we could also select a string value or even a cell reference that contained a value we wanted the formula to return.
While this method is a great way to substitute another value when an error occurs in a cell, Excel actually offers a more streamlined, efficient function for this exact issue.