How To Use IFERROR in Excel (Including Tips)

How To Use The IFERROR Function In Excel – The Easy Way!

Why learn how to use IFERROR in Excel?

Here two primary benefits of using IFERROR:

Find and manage errors

IFERROR can be used to find and manage errors by adding it to formulas or by developing new formulas with it. When adding data to a set, IFERROR enables you to specify a reader-friendly error message. This is useful for tasks like cost charts where entering a zero for the denominator spot could result in a #DIV/0! error. You can set IFERROR to display “$0. 00” instead of the error message. The formula’s IFERROR variable displays the value you specify and holds the errors for that formula.

Identify unintentional errors

Sometimes when you enter a data set and add a formula to a spreadsheet, the formula returns an error. You can enter an error return value, such as “Incorrect Value,” by adding IFERROR before the formula. You can frequently determine the problems by examining the original error return and the IFERROR return. These could be a value that doesn’t agree with the argument or an incorrect data format.

What is IFERROR?

When a formula evaluates an error, the Excel function IFERROR returns an alternative result, such as a blank cell or text. It falls under the category of “logical function,” but it can also be used as a worksheet function. The syntax for IFERROR is:

=IFERROR(value,value_if_error)

The “value” part of the calculation formula is filled in, and the “value_if_error” part is filled in with the desired return. IFERROR(formula,”error display”) causes the function to evaluate the formula’s components. IFERROR returns the formula’s anticipated outcome if there are no errors in the formula. When a formula fails, IFERROR returns the selected display value.

Errors that IFERROR identifies

IFERROR monitors these error returns in a spreadsheet:

How to use IFERROR

You can add IFERROR to your spreadsheet by following these steps:

1. Add an adjacent range to the dataset

If you want to use a row or column to display the IFERROR results, add it. In the new range, duplicate the formula in the cell next to it. This makes it easier to compare the new range with the IFERROR display values to the range with the original error messages. You can decide whether to keep both ranges or just one in the finished spreadsheet.

2. Build the IFERROR argument

Choose the cell that the existing formula is in, then move your cursor between the “=” and the formula in the formula bar. It’s crucial to type “IFERROR” and an open parenthesis before the formula because they define the formula’s order of operations. Create the IFERROR formula, adding the value for the “value_if_error” argument, such as a zero or a text value of your choice. Use the original formula as the “value” argument. You can also instruct IFERROR to return a blank cell by entering a string of unfilled quotes.

Tips for using IFERROR

You can use the following advice when using IFERROR functions in your spreadsheet:

Common text values to use with IFERROR

You can use a variety of error display values depending on the formula and error return in your spreadsheet because IFERROR monitors and catches several error returns. To verify that the formula refers to the proper ranges, use “Incorrect Range” for #NULL! If VLOOKUP returns an error when using IFERROR with it, you can set IFERROR to display “Not Found.” Other typical error messages include “Invalid Value,” “0,” and blank cells displayed with (“”). “.

Use IFERROR to perform an alternate calculation

It’s very common to use IFERROR to display a text for errors, but it can also make a different calculation. Enter a different calculation in the “value_if_error” argument of the “=IFERROR(value,value_if_error)” form. Use the “value_if_error” argument to instruct IFERROR to also check “(D2*B2)” if, for instance, your original formula checks cells B2, C2, and D2 using “(C2+D2)*B2” and returns an error. This might fix the problem or help you fix your original formula.

Please be aware that none of the aforementioned businesses are connected to Indeed.

FAQ

What is the Iferror formula?

If a formula fails, Microsoft Excel’s IFERROR function returns a different value. Errors like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL will be looked for. The Excel built-in function IFERROR falls under the category of a Logical Function.

How use Iferror function in Vlookup?

1. =IFERROR(value, value_if_error)
2. If you want to handle any type of error, use IFERROR.
3. If you only want to handle #N/A errors, which are more frequently the result of the VLOOKUP formula failing to locate the lookup value, use IFNA.

How do I do an Iferror sum in Excel?

The syntax of the array formula will be =SUM(IFERROR(A1:A8,0)). IFERROR is evaluating the data in cells A1 through A8 in this instance, and if there are any errors, it replaces the error value with zero. The SUM function is then used to sum the values. Enter this function is cell A11.