How to use IFERROR for calculations in Excel

Excel is a powerful tool for performing various calculations and data analysis. However, sometimes formulas can return errors due to various reasons, such as invalid input, division by zero, missing references, or incompatible data types. These errors can make your worksheets look messy and unprofessional, and also affect the accuracy of your results.

To avoid these problems, you can use the IFERROR function in Excel to catch and handle errors in your formulas. The IFERROR function checks if a formula or expression returns an error, and if so, it returns a value that you specify. Otherwise, it returns the result of the formula or expression.

In this article, we will show you how to use IFERROR for calculations in Excel with some examples and best practices.

IFERROR function syntax and arguments

The syntax of the IFERROR function in Excel is as follows:

IFERROR (value, value_if_error)

The IFERROR function has two arguments:

value (required) - This is the formula or expression that you want to check for errors. It can be a value, a cell reference, or another formula.
value_if_error (required) - This is the value that you want to return if the value argument returns an error. It can be an empty string (“”), a text message, a number, a date, a logical value (TRUE or FALSE), or another formula.

The IFERROR function can handle all types of errors in Excel, including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

IFERROR examples

Let’s see some examples of how to use IFERROR for calculations in Excel.

Example 1: Return a blank cell if error

Suppose you have two columns of numbers (A and B) and you want to divide them in column C. However, some of the cells in column B are empty or zero, which will cause a division by zero error (#DIV/0!) in column C.

To prevent this error from showing up, you can use the IFERROR function to return a blank cell instead. The formula in cell C2 is:

=IFERROR(A2/B2,"")

This formula checks if the division of A2 by B2 returns an error, and if so, it returns an empty string (“”). Otherwise, it returns the result of the division.

You can copy this formula down to the other cells in column C to get the following result:

A	B	C
210	35	6
55	0
	23

As you can see, the cells that would have returned an error are now blank.

Example 2: Return a custom message if error

Instead of returning a blank cell, you may want to display a custom message if an error occurs. For example, you may want to show “Error in calculation” or “Invalid input” or something else.

To do this, you can use the IFERROR function to return a text message as the value_if_error argument. The formula in cell C2 is:

=IFERROR(A2/B2,"Error in calculation")

This formula checks if the division of A2 by B2 returns an error, and if so, it returns the text “Error in calculation”. Otherwise, it returns the result of the division.

You can copy this formula down to the other cells in column C to get the following result:

A	B	C
210	35	6
55	0	Error in calculation
	23	Error in calculation

As you can see, the cells that would have returned an error now show a custom message.

Example 3: Return another value or formula if error

Sometimes, you may want to return another value or formula if an error occurs. For example, you may want to return zero or one or some other number. Or you may want to perform another calculation or use another function.

To do this, you can use the IFERROR function to return any value or formula as the value_if_error argument. The formula in cell C2 is:

=IFERROR(A2/B2,AVERAGE(A$2:A$4))

This formula checks if the division of A2 by B2 returns an error, and if so, it returns the average of the values in column A from row 2 to row 4. Otherwise, it returns the result of the division.

You can copy this formula down to the other cells in column C to get the following result:

A	B	C
210	35	6
55	0	88.33333333
	23	88.33333333

As you can see, the cells that would have returned an error now show the average of column A.

IFERROR best practices

Here are some tips and best practices for using IFERROR for calculations in Excel:

Use IFERROR to catch and handle errors that are expected or unavoidable, such as division by zero, missing data, or invalid input. Do not use IFERROR to hide or ignore errors that are unexpected or indicate a problem with your data or formula. For example, do not use IFERROR to mask a #REF! error that is caused by deleting a referenced cell or sheet.
Use IFERROR to return a value or formula that makes sense in the context of your calculation and data. For example, if you are calculating a percentage, you may want to return zero if an error occurs, rather than a blank cell or a text message.
Use IFERROR sparingly and only when necessary. Do not use IFERROR to wrap every formula in your worksheet, as this may make your formulas less transparent and harder to debug. Also, using too many IFERROR functions may slow down your workbook performance.
Use IFERROR in combination with other functions to perform more complex tasks. For example, you can use IFERROR with VLOOKUP or INDEX MATCH to handle missing or incorrect values in a lookup table. You can also use IFERROR with array formulas to return an array of results for each cell in a range.

Conclusion

The IFERROR function in Excel is a useful way to catch and handle errors in your formulas and calculations. It allows you to return a value that you specify if an error occurs, or the result of the formula otherwise. You can use IFERROR to return a blank cell, a text message, a number, a date, a logical value, or another formula as the value_if_error argument. You can also use IFERROR with other functions to perform more complex tasks.

We hope this article has helped you learn how to use IFERROR for calculations in Excel. If you have any questions or feedback, please feel free to leave a comment below.

References:

1 - IFERROR function - Microsoft Support 2 - How to use IFERROR in Excel with formula examples - Ablebits 3 - Excel IFERROR Function - How To Use - Excel Trick

How to use IFERROR for calculations in Excel