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: 
 
=(B2-C2)/B2
 
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:
 
=IF(ISERROR((B2-C2)/B2),"-",(B2-C2)/B2)
 
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:
 
=IFERROR((B2-C2)/B2,"-")
 
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!:
 
=IF(ISNA(VLOOKUP(A8,$A$1:$D$5,4,0)),0,VLOOKUP(A8,$A$1:$D$5,4,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 david@acctadv.com 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...

Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....

Upcoming CPE Webinars

Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.