IFERROR function in Excel, formulas, and usage

The IFERROR function in Excel is a basic Excel function, a logical function that returns the desired value when its condition produces an error. While we calculate data in Excel, it is inevitable to get errors when doing it. Excel errors can occur in any function like Vlookup, . with many different error codes like the #VALUE date error in Excel.

The IFERROR function will help us detect errors in the data sheet and handle them. The value returned will depend on the user specified when the function is wrong. The following article will guide you how to use the IFERROR function in Excel.

1. How to use the SUMIF function in Excel?
2. 3 ways to calculate totals in Excel
3. How to use the Hlookup function in Excel?
4. How to fix Errors were detected while saving file in Excel 2010

1. Excel IFERROR function formula

The IFERROR function has the formula is = IFERROR (value, value_if_error) . Inside:

1. Value: This is the argument to check. Can be calculations, formulas, Excel functions, are required values.
2. Value_if_error: This is the return value that specifies if the formula fails.
3. The Value_if_error value you can declare is a space (''), equal to 0, an example message line 'Error result', .
4. Value_if_error value is returned when the formula returns values ​​with error types such as: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME? or #NULL !.

2. How to use the IFERROR function in Excel

Download the IFERROR function exercise in Excel with the answer below.

1. Download the Excel IFERROR function exercise

Example 1: Calculation of average score of the error

We have the following subjects transcript and are required to average an average of 3 subjects through the calculation, without using the AVERAGE function.

IFERROR function in Excel, formulas, and usage Picture 1

First we enter the average calculation as normal and then press Enter to produce the result. Then drag the first result cell down to the remaining cells.

IFERROR function in Excel, formulas, and usage Picture 2

It can be seen that the data cells below have an error, so the error results as shown in the picture. If we leave it like that, the data sheet is pretty bad. Instead of such an error, you can use the IFERROR function to replace it with another value.

IFERROR function in Excel, formulas, and usage Picture 3

We enter the formula as = IFERROR ((C2 + D2 + E2) / F2, "Wrong calculation") and press Enter.

IFERROR function in Excel, formulas, and usage Picture 4

When the data in each cell is correct, display the result. You scroll down to the remaining cells to check. If a cell has an error, it will produce the following calculation error.

IFERROR function in Excel, formulas, and usage Picture 5

Example 2: Refining% of items sold

We have the following table summarizing the quantities of stock sold and stock taken. We will calculate% in Excel by dividing the stock sold to the inventory to find the% value we are looking for.

IFERROR function in Excel, formulas, and usage Picture 6

Enter = C2 / D2 and press Enter and produce the result.

IFERROR function in Excel, formulas, and usage Picture 7

In the table there are some values ​​in the sold cell and the warehouse total error so when calculating% has displayed an error as shown below. We can also replace this error with another value.

IFERROR function in Excel, formulas, and usage Picture 8

In the first cell enter the formula = IFERROR (C2 / D2, 'Wrong error entered') and press Enter.

IFERROR function in Excel, formulas, and usage Picture 9

With the correct input data, the results are still displayed. As for the wrong input box will replace with the message as below.

IFERROR function in Excel, formulas, and usage Picture 10

So the Excel IFERROR function is very easy to use. We just need to enter the function name and then the formula and the replacement value for the error is okay. Users can enter alternate values ​​as they like.

I wish you successful implementation!