The Excel IFERROR Function is a simple but yet elegant function that helps you tidy up formulas that calculate into an error. With the IFERROR Function you are able to return a custom output should a formula generate an error. If in a situation the initial formula works fine and returns an output as expected, then the IFERROR function returns the standard result generated by the initial formula.
To reiterate, the IFERROR Function is a conditional function that returns a value specified by the user if a formula returns an error. In today’s post, we would take you through the IFERROR function and all you need to know about it. Let’s first start with the Syntax & Argument, then we move on to usage notes and examples.
Syntax & Argument
The syntax of the IFERROR Function is as follows:
=IFERROR(value, value_if_error)
There are two (2) arguments and both are required:
- Value: This is the formula you want to evaluate
- Value_if_error: The value you want to return if a formula returns an error.
There are some things to note when using the IFERROR Function, and we would go over them in the next section.
Usage Notes
- The IFERROR Function is used to catch and handle errors that may occur when using other formulas in excel. With the function, you are able to specify the value to display if there is an error. This could prove very useful when dealing with large data sets as you would be able to save time and reduce errors.
- The IFERROR can be used for basic formulas such as B1/C2 and can also be nested in more complex formulas such as VLOOKUP, HLOOKUP, INDEX MATCH and many others to deal with any error that occur when using those functions.
- The IFERROR Function can catch and handle a host of errors such as the #DIV/0!, #VALUE!, #N/A!, and #REF! errors.
- The IFERROR return exactly what the user specifies as value if error. This could be another value or a formula. This means you should be sure of your value if error especially when are working with a large data set and won’t have time to double check.
IFERROR Function Examples
In the examples section, we would be taking a look at the 4 major types of errors the IFERROR Function catches and handles as discussed in the Usage notes section. Let’s start things off with the #DIV/0! error.
IFERROR on #DIV/0! Errors
In our data set for this illustration, we have the Earnings Before Interest & Tax (EBIT) of our portfolio company along with its Interest Expense for the year. We can derive the Interest Coverage ratio by simply dividing the EBIT by the Interest Expense. But as you would see, in a period where there is no interest for the year, our formula would result in a #DIV/0! Error
By using the IFERROR Function, we can specify a custom value rather than the #DIV/0! error displayed on cell D4. Let’s update the formula by introducing the IFERROR:
You can copy the formula to the cell above (E3) and the other cells below (E5:E7) and their values remain unchanged because they do not result in an error.
IFEROR on #VALUE Errors
Now let’s Imagine that our Fixed Service Coverage ratio is usually half of our Interest Service Coverage Ratio, If we divide the previous Interest Coverage by 2, we have a #VALUE error for year 20X2 because the Interest Coverage ratio value is a Text.
Like the previous example, we just need to wrap the existing function in an IFERROR Function to get rid of the #VALUE Error.
Copy and paste the function to the cell above (E3) and the other cells below (E5:E7) and you are done.
IFERROR on #N/A Errors
We would illustrate using the VLOOKUP function. We have a data set containing outbound minutes made by subscribers of a Telecommunications company to various international destinations.
You can see from the above image that when we look up a destination not on our original list of destinations on column B, our VLOOKUP Formula returns a #N/A value. Now let’s wrap the VLOOKUP Formula in an IFERROR Function to return zero (0) if not available.
IFERROR on # REF Errors
There is a plethora of ways one can get a #REF error, and they include:
- Deleting or moving cells that are referred to in a formula
- Copying and pasting with relative references and moving outside the range of normal cells in excel
- Using a range that is not the same size as the range referred to in a formula
- Deleting a sheet that contains input for cells in another worksheet
- Using a range that contains errors or non-numeric data in a formula that requires numeric data
For our illustration, we have information on diesel purchase for our business, and we are to calculate the total cost of diesel for each month. You would however notice that we are referring to input cells in another workbook and there is an error in one of our references.
The name of the external workbook we are linking to is titled “Diesel Purchased” and as you would see in cell C8, we mistakenly omitted the d at the end.
Now we can either choose to update the reference or use the IFERROR function to return a zero (0) if we run into a #REF Error. For the purpose of this illustration, let’s choose to ignore the wrong reference and return a 0 instead. We only need to wrap the existing function in an IFERROR Function to make this work
