Excel IFERROR Function

The Excel IFERROR function is used to handle errors that may occur in a formula, by returning a specific value if an error occurs, and returning the result of the formula if there is no error.

IFERROR Summary

IFERROR Purpose

The purpose of the IFERROR function is to handle errors that may occur in a formula and to return a specified value if the formula produces an error.

IFERROR Arguments

The IFERROR function takes two arguments:

• value (required) – This is the value or expression to be evaluated for errors.
• value_if_error (required) – This is the value that is returned if the value argument results in an error.

IFERROR Return Value

The IFERROR function returns the result of the value argument if there is no error. If an error occurs, it returns the value_if_error argument.

IFERROR Syntax

The syntax of the IFERROR function is as follows:

• IFERROR(value, value_if_error)

Here’s how the IFERROR function works

• You specify a value or expression to be evaluated for errors as the first argument.
• You specify the value that should be returned if the value or expression results in an error as the second argument.
• If the value or expression does not result in an error, the IFERROR function returns the result of the value or expression.
• If the value or expression results in an error, the IFERROR function returns the value specified in the second argument.

Example of IFERROR

Here’s an example of how to use the IFERROR function in Excel:

Suppose you have a formula that divides the value in cell A1 by the value in cell B1, and you want to display a message if an error occurs.

You can use the following formula:

=IFERROR(A1/B1, "Error: Cannot divide by zero")

This formula will return the result of A1/B1 if there is no error.

If an error occurs (for example, if B1 contains a zero). It will return the message “Error: Cannot divide by zero”.

IFERROR function + Sum Function

Suppose you have a worksheet that contains a column of numbers and you want to calculate the sum of those numbers.

However, some of the cells in the column may contain errors or blank cells. Which would cause the SUM function to return an error. To handle this error, you can use the IFERROR function.

Here’s an example formula that uses the IFERROR function:

=IFERROR(SUM(A1:A10), 0)

In this formula, SUM(A1:A10) calculates the sum of the values in cells A1 through A10.

If there are any errors in those cells, the IFERROR function will return 0 instead of the error message.

So if, for example, the values in cells A1 through A10 are 10, 20, 30, “apple”, 40, “”, 50, 60, #DIV/0!, and 70, the formula would return 0, since the fourth and ninth cells contain errors.

Without the IFERROR function, the formula would return an error message.

This is just two example of how you can use the IFERROR function in Excel to handle errors in formulas. It’s a useful tool that can help ensure that your calculations are accurate, even if there are errors or blank cells in your data.

Choosing Between IFERROR and IFNA Functions

The IFERROR function can be helpful, but it may not be the most precise solution as it captures a wide range of errors. For instance, if there’s a mistake in a formula, Excel may show the #NAME? error, and using IFERROR will mask the error and present an alternate outcome. This may conceal a significant issue. IFNA function is often a better option, as it only catches the #N/A error in most situations.

Other Excel Error Functions

Excel has several functions related to errors, each with distinct behaviors, such as:

• ISERR function returns TRUE for any error type excluding #N/A error.
• ISERROR function returns TRUE for any error.
• ISNA function returns TRUE only for #N/A errors.
• ERROR.TYPE function returns a numeric code for a specific error.
• IFERROR function catches errors and offers an alternate outcome.
• IFNA function catches only #N/A errors and offers an alternate outcome.

Some important notes to keep in mind

• If the value is empty, it will be considered as an empty string (“”) and not an error.
• If the value_if_error is given as an empty string (“”), no message will appear when an error is detected.
• In Excel 2013 and later versions, you can utilize the IFNA function to capture and manage #N/A errors specifically.