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.
A previous version of this article first appeared on www.accountingweb.com .
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
