IFERROR

In this comprehensive guide, we will explore everything you need to know about the IFERROR function in Excel. The IFERROR function is a powerful tool that allows you to handle errors in your formulas gracefully. It is particularly useful when you want to display a custom message or value instead of the default error messages that Excel generates, such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. By the end of this article, you will have a deep understanding of the IFERROR function, its syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae.

IFERROR Syntax

The syntax for the IFERROR function is as follows:

=IFERROR(value, value_if_error)

Where:

• value is the expression or formula that you want to evaluate for errors.
• value_if_error is the value or expression that you want to return if an error is found in the ‘value’ argument.

The IFERROR function checks the ‘value’ argument for any errors. If an error is detected, it returns the ‘value_if_error’ argument. If no error is found, it returns the result of the ‘value’ argument.

IFERROR Examples

Let’s look at some examples of how to use the IFERROR function in Excel.

Example 1: Handling division by zero

Suppose you have a formula that divides two numbers, A1 and B1:

=A1/B1

If B1 is zero, this formula will return a #DIV/0! error. To handle this error, you can use the IFERROR function:

=IFERROR(A1/B1, “Cannot divide by zero”)

In this case, if B1 is zero, the formula will return “Cannot divide by zero” instead of the #DIV/0! error.

Example 2: Handling errors in VLOOKUP

Imagine you are using the VLOOKUP function to search for a value in a table, and you want to display “Not found” if the value is not in the table. You can use the IFERROR function to achieve this:

=IFERROR(VLOOKUP(search_value, table_range, column_index, FALSE), “Not found”)

If the VLOOKUP function returns an error (e.g., #N/A), the formula will return “Not found” instead.

Example 3: Handling errors in nested formulas

You can also use the IFERROR function to handle errors in nested formulas. For example, suppose you have a formula that calculates the percentage change between two numbers, A1 and B1:

=(B1-A1)/A1

If A1 is zero, this formula will return a #DIV/0! error. To handle this error, you can use the IFERROR function:

=IFERROR((B1-A1)/A1, “N/A”)

In this case, if A1 is zero, the formula will return “N/A” instead of the #DIV/0! error.

IFERROR Tips & Tricks

Here are some tips and tricks to help you get the most out of the IFERROR function:

1. Use the IFERROR function to handle errors in complex formulas, especially when you have multiple nested functions that can potentially return errors.
2. When using the IFERROR function, make sure to provide a meaningful ‘value_if_error’ argument that helps users understand why an error occurred.
3. Remember that the IFERROR function only handles errors in the ‘value’ argument. If there are errors in the ‘value_if_error’ argument, the function will not handle them.
4. Consider using the IFNA function if you only want to handle #N/A errors and let other errors propagate through your formula.

Common Mistakes When Using IFERROR

Here are some common mistakes that users make when using the IFERROR function:

1. Using the wrong function name: Make sure to use “IFERROR” and not “IFERR” or “IF_ERROR”.
2. Forgetting to close the parentheses: Ensure that you have the correct number of closing parentheses in your formula.
3. Not providing a meaningful ‘value_if_error’ argument: It is essential to provide a helpful message or value that helps users understand why an error occurred.
4. Using the IFERROR function to mask all errors, even when some errors should be addressed: Be cautious when using the IFERROR function, as it can sometimes hide errors that need to be fixed in your formula or data.

Why Isn’t My IFERROR Working?

If your IFERROR function is not working as expected, consider the following troubleshooting steps:

1. Check the syntax of your formula: Make sure you have the correct number of arguments and parentheses.
2. Ensure that the ‘value’ argument is the expression or formula that you want to evaluate for errors.
3. Verify that the ‘value_if_error’ argument is the value or expression that you want to return if an error is found in the ‘value’ argument.
4. Remember that the IFERROR function only handles errors in the ‘value’ argument. If there are errors in the ‘value_if_error’ argument, the function will not handle them.

IFERROR: Related Formulae

Here are some related formulae that you might find useful when working with the IFERROR function:

1. IFNA: This function is similar to IFERROR but only handles #N/A errors. Use this function if you want to handle #N/A errors specifically and let other errors propagate through your formula.
2. ISERROR: This function returns TRUE if the given value is an error and FALSE otherwise. You can use this function in combination with the IF function to handle errors in a more customized way.
3. IF: The IF function allows you to perform conditional calculations based on a logical test. You can use this function in combination with the ISERROR function to handle errors in a more customized way.
4. ERROR.TYPE: This function returns a number corresponding to the type of error in a given cell. You can use this function to identify the specific type of error and handle it accordingly.
5. ISERR: This function returns TRUE if the given value is an error other than #N/A and FALSE otherwise. You can use this function in combination with the IF function to handle specific types of errors.

By now, you should have a thorough understanding of the IFERROR function in Excel, its syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae. With this knowledge, you can confidently use the IFERROR function to handle errors in your formulas and create more robust and user-friendly spreadsheets.