How to Use the IFERROR Function in Excel

The IFERROR function in Excel is a useful way to handle errors that may occur in your formulas. It allows you to specify a default value or action to take when an error is encountered, instead of displaying the error message. This can make your spreadsheet more user-friendly and prevent unwanted results.

Basic Syntax of the IFERROR Function

The basic syntax of the IFERROR function is:

=IFERROR(value, value_if_error)

where:

• value is the expression or formula that you want to evaluate.
• value_if_error is the value or formula that you want to return or perform if an error occurs in value.

The IFERROR function can handle any type of error, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

How to Use the IFERROR Function in Excel

To use the IFERROR function in Excel, follow these steps:

1. Enter your formula or expression in a cell, or select an existing cell that contains a formula or expression.
2. Wrap the formula or expression with the IFERROR function, and provide a default value or action for the value_if_error argument. For example, you can enter a blank cell, a text message, a zero, another formula, or a reference to another cell.
3. Press Enter to complete the function and see the result. If there is no error in the original formula or expression, the IFERROR function will return the same value. If there is an error, the IFERROR function will return the default value or action that you specified.

Example of Using the IFERROR Function in Excel

Let’s say you have a table of sales data for different products and regions, and you want to calculate the average sales per product and region. However, some of the cells in the table are blank or contain text, which will cause an error when you try to use the AVERAGE function. To avoid this, you can use the IFERROR function to display a zero instead of the error message. Here is how you can do it:

1. In cell D2, enter the formula =AVERAGE(B2:C2). This will calculate the average sales for product A in regions X and Y. The result is 150.
2. In cell D3, enter the formula =AVERAGE(B3:C3). This will calculate the average sales for product A in regions Y and Z. However, since cell C3 is blank, the result is an error message #DIV/0!.
3. To fix this, wrap the formula in cell D3 with the IFERROR function, and enter 0 as the default value. The formula becomes =IFERROR(AVERAGE(B3:C3),0). The result is 0.
4. Copy the formula in cell D3 and paste it in cells D4 to D9. This will apply the IFERROR function to the rest of the table and display zeros instead of error messages.
5. The final table looks like this:

Other Approaches to Handle Errors in Excel

The IFERROR function is not the only way to handle errors in Excel. Depending on your situation, you may want to use other functions or methods, such as:

• The IFNA function, which returns a default value only if the error is #N/A.
• The ISERROR function, which returns TRUE if the value is an error, and FALSE otherwise. You can combine this with the IF function to perform different actions based on the error status.
• The ERROR.TYPE function, which returns a number that corresponds to the type of error. You can use this with the CHOOSE function to return different values or actions for different types of errors.
• The Data Validation feature, which allows you to set rules and restrictions for the data that can be entered in a cell or range. This can prevent errors from occurring in the first place.
• The Formula Auditing tools, which help you identify and correct errors in your formulas. You can use the Error Checking button, the Trace Error button, the Evaluate Formula dialog box, and the Watch Window to find and fix errors in your spreadsheet.