Excel IFERROR() function
The IFERROR() is a logical function of Excel. It is a special function designed to catch and handle errors in formulas and calculations. Sometime while working with Excel formulas, there occurs some unexpected errors because of the improper cell value that we have used. In such cases IFERROR function allows us to replace those error messages with any other different values.
To be more specific, IFERROR checks the Excel formula, and if the formula triggers an error, the function returns another value (blank, custom text, number value, boolean) that you specify; else, if the formula doesn't evaluate any error, it simply returns the output of the formula.
This function works on 9 formula errors generated by Excel wherever the user use any improper value or data type, such as - #N/A, #NUM!, #VALUE!, #REF!, #NAME?, #DIV/0!, or #NULL! Error.
IFERROR (Value, [Value_if_error])
Value (required): This parameter represents the first value that is evaluated to see if there occurs an error.
Value_if_error (optional): This parameter replaces the error value by this value if there is an error in the cell.
Example 1: In the below case, we have divided Data A by Data B and have displayed two outputs, one using IFERROR and one without using the IFERROR function.
As you can see in the above example, on dividing the data value 1 by 0, Excel has thrown an error, i.e. '#DIV/0!'. So using IFERROR, we can give values we want unlike here where we have replaced the error with numeric data i.e, 999. You can also enter text, special symbols or some logical values which can let the user know that there is some problem with the data.
Example 2: Divide Data A by Data B and display the output without using IFERROR function and with using IFERROR function.
You can also provide customized text messages to indicate the user that there is some problem with the data.
As you can see in the above example, on dividing 1 by a, we get an error (as we cannot divide a number with alphabet), without using IFERROR we get #VALUE! i.e. an error. So using IFERROR, we can give values we want unlike here we have replaced the error with a customized message i.e, "DATA IS WRONG".
Now whenever the IFERROR finds an error it will show the same text message. You can also enter number, special symbols or some logical values which can let the user know that there is some problem with the data.
Example 3: Write an Excel formula in the below data table using IFERROR to find the monthly salary of each employee using data in Column C and if there occurs an error, replace the error message with 'ERROR IN SALARY'. Change the format to £ (Pounds) and no decimal places (use the Round Function to avoid decimal).
In the above question, we will use a combination of IFERROR and Round function. Where IFERROR function will help catch and handle errors in formulas, and if an error appears, then change it to ERROR IN SALARY. In contrast, the ROUND function will round all the calculations (if there is no error). We know salary figures are always in number, but for E003, the salary is 'abcd' which is not a valid number. Therefore, it will throw an error in this case, and we will catch and handle the error using the IFERROR function. Refer to the below image for the solution to the above question.
That's it! Now, you can freely use the Excel IFERROR function to trap and manage errors the way you want.
Things to remember while working with Excel IFERROR function
IFERROR vs. IF ISERROR
Now, as we have already discovered about the IFERROR function, its usage, syntax, parameters, implementation, you may question why some Excel users are still inclined towards using the ISERROR function (with a combination of IF). Also, you may wonder about the difference between the IFERROR function and ISERROR function in EXcel, as both sounds the same.
In the following table, we briefly discussed regarding these two major uncertainties:
Best practices for using IFERROR in Excel
By now, we have understood the logic behind the IFERROR function and perceive that it is the easiest way to trap and manage errors in Microsoft Excel. It easily replaces the error values with blank cells, numeric data or custom text messages. However, that does not imply you should cover every Excel formula with the IFERROR function, and therefore the following suggestions may help you hold the balance.
Next TopicExcel Median() function