Trapping Errors within Excel Formulas

By David H. Ringstrom, CPA

From time to time you may craft a formula that in some cases will return an error, such as #DIV/0! or #N/A. Many users overcome this situation by using combinations of the IF, ISERROR, and ISNA worksheet functions. However, using these functions in concert can sometimes result in unnecessarily complex formulas. In this article, I'll discuss an alternative available to anyone using Excel 2007 or later.
Let's first start with a common scenario, where calculating the percentage change between two numbers results in a #DIV/0! error, as illustrated in Figure 1. Presently, the formula in cell D2 takes this form: 
Figure 1: Excel returns a #DIV/0! error when you attempt to divide by zero.
As you can see, when we copy this formula down the column, D4 returns #DIV/0! because cell B4 equals zero. To eliminate the #DIV/0! error, we can amend the formula to take this form:
As shown in Figure 2, the formula in cell D4 now returns a dash instead of #DIV/0!.
Figure 2: A combination of IF and ISERROR can display alternative results when a calculation results in an error.
For the uninitiated, the IF function has three arguments, or inputs, which are:
  • logical_test – In this case, the ISERROR function carries out the test and either returns TRUE or FALSE.
  • value_if_true – If ISERROR returns TRUE, we want Excel to return a dash, which we must enclose in double quotes. The quotes are only required when we want the IF statement to return text, so there's no need to enclose numeric values, such as zero, in quotes.
  • value_if_false – If ISERROR returns FALSE, we want Excel to perform our original calculation.
Although valid, this approach introduces complexity to the formula because we must repeat our original calculation twice. However, as shown in Figure 3, if our spreadsheet will only be used in Excel 2007 or later, we can streamline the formula to this:
Figure 3: The IFERROR function streamlines complex error-trapping formulas.
First introduced in Excel 2007, IFERROR has two arguments: 
  • value – This is the calculation we wish to test for errors.
  • value_if_error – This is the result we wish to display should our calculation return an error.
As you can see, IFERROR eliminates the need to repeat any portion of the calculation. However, it isn’t backwards compatible with earlier versions of Excel. As shown in Figure 4, the IFERROR function will return #NAME? in Excel 2003 and earlier. If you need compatibility with earlier versions of Excel, use the aforementioned IF/ISERROR approach instead.  
Figure 4: The IFERROR function is incompatible with Excel 2003 and earlier, but can be used in Excel 2007 and later.
Also, a possible downside to both IFERROR and ISERROR is that they mask any # sign error that your formula may trigger. You may have situations where you want to mask a #N/A error but be notified of other errors, such as #DIV/0!, #REF!, or #NAME?. As shown in Figure 5, you can craft a formula to trap #N/A errors only, but display other # sign errors like #DIV/0!:
Figure 5: ISNA tests specifically for #N/A errors but will allow other errors to be displayed.
Sharp-eyed readers will notice that I used a zero in place of the typical FALSE within VLOOKUP to indicate an exact match. If you're unfamiliar with VLOOKUP, it's a function that's used to return values from a list that has four inputs:
  • lookup_value – This represents the item that you're looking for within a list. For instance, in the formula above, A8 signifies that we want to look for the word “Apples.”
  • table_array –This represents the cell coordinates for the list you wish to search. In the formula above, our list is comprised of cells A1 through D5. VLOOKUP searches for the lookup_value in the first column of the table_array.
  • col_index_num – This argument tells VLOOKUP which column you want to return a value from when a match is found in the first column. In this case we want to return the % change from the fourth column of our table_array.
  • range_lookup – Use this cryptic setting to signify if you want an exact match or an approximate match. In this case, we want an exact match on the word “Apples,” so enter the word FALSE or a zero in this final argument. Omit this argument, or use the word TRUE or a one in that position to signify an approximate match, such as if you were looking up a tax bracket based on a gross income number.
Read more articles by David Ringstrom. 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.


You may like these other stories...

You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Oct 30
Many Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word documents, databases, and even web pages.