Deal with formula errors

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Errors occur in Excel formulas for a number of reasons, such as a doing a lookup formula ( VLOOKUP] or INDEX/MATCH ) for a value that cannot be found which returns a #N/A error value as a result.

Sometimes these errors are expected and so you can trap and deal with them (to prevent them displaying as error values) but error trapping potentially masks genuine errors in the workbook and so should be used sparingly! It’s often better to let errors display but then know how to locate them and then identify/fix the cause. Just as you would do a spell check in a file, it is worth periodically checking for errors (e.g. before saving files!) – Excel has an ‘Error Checking’ functionality located in the Formula Auditing section of the Formulas tab on the ribbon which will allow you to do this.

Of course the ideal approach is to try and prevent errors from occurring in the first place by writing formulas in a robust enough way and managing the source/input data to ensure that formulas based on it should return expected results. For example if you have a formula of Input X divided by Input Y then put data validation around Input Y to not allow zero values, hence eliminating the possibility of getting a #DIV/0 error.

Excel has built in functions for trapping errors-you can write a formula and then wrap the IFERROR function around it to tell Excel what to return in the event that your formula returns an error.

The syntax of the IFERROR function is =IFERROR (formula, value if error). The ‘value if error’ is simply whatever you want to display if the formula evaluates to an error-this could be 0 (zero) or “” (an empty string) for example.

So if you want to return 0 in the event of an error, start with whatever formula you have, put IFERROR( after the equals sign and then go to the end of the formula and type ,0). An example of a MATCH function with this applied is =IFERROR(MATCH(A2,B:B,0),0).

A word of caution with doing this however-the IFERROR function traps and eliminates all types of errors regardless of why they occurred. This can often obscure genuine problems with the formula/spreadsheet and lead to misstatement.

For example, you might be doing a VLOOKUP or INDEX/MATCH in full knowledge that some of the entries you’re looking up won’t exist in the range and just wanting to ignore these by returning zero or an empty string. But the IFERROR function doesn’t discriminate between errors-if the Lookup formula referred to a named range that no longer exists it would return a #Name error but when the formula is trapped by IFERROR it would still return 0. This could potentially lead you to believe that the entry being looked up doesn’t exist when it fact it does but there’s another underlying issue which is now obscured.

In this case, it’s better to use the more specific ISNA function which allows you to error trap the specific error (#N/A) returned when a lookup value cannot be found in the range, whilst still displaying other types of errors.

The ISNA function takes a single parameter-the formula being evaluated-and returns TRUE if the formula evaluates to a #N/A error and FALSE if not. You can then map this around an IF function to determine what to return in the event of a #N/A error. For an example of how this works see comparing lists using a lookup formula.

Excel also has a function which returns the type of error called ERROR.TYPE. This function takes one argument (errror_val ) and returns a number indicating the type of error. For example wrapping a lookup formula which returns a #N/A error around an ERROR.TYPE function

e.g. =ERROR.TYPE(MATCH(A2,A:A,0)) would return 7 because 7 represents the error type of #N/A. You could then use an IF function with this to return particular values in the event of a particular type of error, or a lookup against a range of error types to return different error messages depending on the type of error. To see all the error numbers returned by the ERROR.TYPE function start typing the function by typing =ERROR.TYPE( and the intelli-sense that comes up (in Excel 2010 only) will show the error types and corresponding numbers. These are shown in the ‘Error Types’ worksheet of the Topic Example Workbook which also shows the different types of errors and possible reasons why they might exist. Note that ERROR.TYPE returns N/A in the event that the formula doesn’t return an error.

Provided errors aren’t trapped, there are several ways to navigate to errors within a spread sheet:

• Use the Error Checking functionality located in the Formula Auditing section of the Formulas tab – this will navigate to any errors in the current worksheet (so needs to be run separately for all worksheets containing formulas).
• Use the Go to (Ctrl + G] -> Special Functionality and select type of cell ‘ errors ‘ (one of the formula check boxes) to see general errors.
• Use [conditional formatting] to highlight error cells within a range (e.g using a ISERROR formula).

You can also see all the formulas used in your workbook at a glance (without having to go into individual cells) by using the display option of showing formulas instead of cell values. To do this, click on ‘Show Formulas’ in the Formula Auditing section of the Formulas tab. This is also very useful for doing an audit of the workbook – making sure your formulas are consistent and don’t contain ‘magic numbers’ (hard-coded numbers added or subtracted at the end of a formula as an adjustment! Highlighting a row or column and then going to Goto (Ctrl + G) -> Special and select row differences/column differences highlights cells which are different from the first formula in that row/column. This will potentially highlight where formulas are inconsistent and therefore may be returning the wrong result.

Once you are in a cell which evaluates to an error, if the formula is quite complex it may not be obvious why the error is occurring. In this case you then need to break down each element of the formula to find which specific part is causing the problem. For this there are several useful techniques:

• Using the ‘ evaluate formula’ functionality (located within the formulas tab): this allows you to step through the various calculation elements within the formula so you can see which part is causing an error. Once in the dialog box, click ‘Evaluate’ to see the result of the underlined part of the formula, and move to the next element, or ‘Step in’ to break it down further.
• Going into edit mode of the cell (pressing F2), highlighting the part of the formula you want to evaluate and pressing F9. (Click escape when you are done rather than edit, otherwise that formula element will remain a static hard-coded value!)
• Tracing Precedents/Dependants: This allows you to see visually all the cells that link to the formula and that the formula links to (both directly and indirectly). Clicking on either of these will show a series of arrows – the solid lines are those that link to/from the cell within the same worksheet, and the dotted lines refer to links onto/from another worksheet. Double clicking on the dotted line arrow will bring up all the references in the GoTo dialog box which you can then use to navigate to.
• Using the ‘Error Checking’/‘Trace Error’ functionalities ( also under ‘formula auditing’ in the Formulas tab). The Trace error functionality shows the source of errors through arrows (in a similar way to tracing precedents), and the error checking tool provides an interface for accessing the formula auditing functionality outlined above.

In the Topic Example Workbook, fill in the yellow cells in the ‘Error Trapping’ worksheet as follows:

• In cell K5 write a lookup formula that looks up the value in cell I5 (which doesn’t exist so this formula should return a ‘#N/A’ error)
• Repeat this formula in cell K7 but error trap it using the IFERROR function, returning the text “Unknown Category” in the event of an error
• In cell K9 use ISNA to handle specific #N/A error types, returning the text “Unknown Category” in the event of a #N/A error
• In cell K14, repeat the formula in cell K12 (which returns a ‘#NAME?’ error) but error trap it using IFERROR, also returning the text “Unknown Category” in the event of an error
• In cell K16, repeat the formula in cell K12 (which returns a ‘#NAME?’ error) but error trap it using ISNA, returning the text “Unknown Category” in the event of a #N/A error (which in this case should continue to return #NAME?#)
• In cell K19, use the ERROR.TYPE function to return the error number returned by the formula in cell K12 (Reference cell K12 rather than repeating the formula). Lookup this error type number against the error types [table] in the ‘Error Types’ worksheet. Use ISNA to return the value in K12 in the event that it doesn’t evaluate to an error.

A demonstration is shown in the video clips below. Resist the temptation to view the answers file below until you have tried this!

IFERROR and ISNA

IFERROR and ERROR.TYPE

Formula Auditing

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Remove the #N/A Error from VLOOKUP in Excel