Error handling in Excel using Formulas

Sometime when we use formulas, we get error such as #REF! or #DIV/0!. Many times we need to know when a formula error occurs, but in some cases, we will face a situation to avoid error messages.
To avoid error message in formulas Excel 2010 provides two functions namely ISERROR and IFERROR. Let us look into each of these functions using the example.

In the above example Column D calculates average sales per Rep. So the formula in Cell D2 is  =B2/C2.
Using the ISERROR function:
As you see in the above example, the formula displays an error if the cells used in the calculation are empty. If you would like to hide those errors, you can do so by using and IF function to check for an error. For this example, change the formula in cell D2 as mentioned below.

IFERROR function examples

The ISERROR function returns TRUE if its argument evaluates to an error. In such a case, the IF function returns and empty string. Otherwise, the IF function returns the calculated value. As you see in the below image, when this formula is copied down the column, the result is a bit more visually pleasing.
You can adapt this technique to any formula.  The original formula (Without the initial equal sign) serves as the argument for the ISERROR function and it repeats as the last argument of the IF function.
So below is the syntax for using IF and ISERROR function:
=IF(ISERROR(“Criteria to be checked for Error”),”What you want to return in case of error”, “Formula to be calculated”) 
You can put anything you like as the second argument for the ISERROR function.  in the example we have used empty string but you can use any thing. You might chose another formual to executed or any cell reference or any text  or value.
Using the IFERROR function:
IFERROR function takes two argument. The first argument is the expression that’s checked for an error and the second is the value to return if the formula evaluates to an error. Then formula used in the above example becomes like this.

Using this functions has two major advantages;

  1. Writing error checking formula is easier because IFERROR does the work of both the IF function and the ISERROR function.
  2. The expression is evaluated only one time which can result in faster recalculation time.
Note: IFERROR was intriduced in Excel 2007 and it does not work with earlier versions of Excel.
0 comments… add one

Leave a Comment