Why You Should Be Cautious About Using Excel’s IFERROR Formula

by Matthew Kuo on November 8, 2015

in Error Checking, Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

IFERROR_00

The IFERROR formula was designed to solve a common aesthetic problem that most of us have encountered when using Excel – when we know that there are errors in our data, but we’d prefer not to see Excel’s standard error message notation.  Error messages usually consist of all caps lettering that is preceded with the “#” symbol, and some of them have an exclamation point or question mark at the end.  Visually, Excel’s error messaging is intended to be “jarring” because Excel’s developers want you to alert you that something might be wrong with your file.  But if the error is expected or benign, such a loud visual indicator may not be necessary.

Enter the IFERROR formula.  IFERROR has become very popular because it allows you to mask Excel’s error message with a blank space, or a custom message of your own, such as “Not Found“.  For example, if I were to run a VLOOKUP formula on several numbers, and I knew one of the values was not going to be found within my data set, there are situations where I’d much rather see the phrase “Not Found” than the “#N/A” message.  This is especially true if I need to present this output in a more formal medium, such as a PowerPoint deck.

In the example below, imagine I was pulling rates by city from a larger data set, but I knew that Detroit and Mexico City would not be found.  The table on the right shows how you can improve the visual design of your table by using IFERROR to get rid of Excel’s “#N/A” error message.

IFERROR_01

While this is clearly a great use case for IFERROR, the key problem with using the formula is this:

IFERROR masks ALL possible Excel error types, even the ones you were not originally intending to mask

The IFERROR Formula & Syntax

Let’s start by reviewing the IFERROR formula and its syntax:

= IFERROR ( value, value_if_error )

The syntax itself is fairly simple.  Where it asks you for the value, put in your original intended formula, and where it asks for value_if_error, input the value you want to show if that original formula returns an error.

In the example above, we were using a simple VLOOKUP to pull a value from a table.  To add IFERROR to that formula, all I need to do is add the IFERROR formula prompt and indicate the value I want to show for the error condition, which is in this case “Not Found“.

IFERROR_02

The Problem with IFERROR

IFERROR becomes problematic when you encounter an error condition in your data set that you were not expecting.  We’ll continue from the previous example, but this time show the source data table that the VLOOKUP was pulling from.  In the image below, we have the same IFERROR setup to lookup values from one table to another.

IFERROR_03

You can see that Seattle and Los Angeles are pulling correctly because they both appear in the source table.

Detroit and Mexico City are both also calculating correctly, because they do not appear in the source and therefore are indicated with “Not Found“.

The problem lies with San Francisco, which shows “Not Found” but is actually there in the source table; the value just happens to have a #DIV/0! error in it.  Because IFERROR masks all error types, it ends up implying that San Francisco is not in the source table at all.

This IFERROR issue can cause huge issues, as it is not limited to just masking #DIV/0 errors.  The errors for #REF!, #NAME?, #VALUE! (just to name a few), all of which are worth your attention, would all also be masked by the IFERROR formula.  For any data sensitive situation, I’d rather have these error messages pop-up and tell me that I need to fix my data set, rather than show a table that is aesthetically pleasing, but fundamentally incorrect.

Using IF ISNA to Mask #N/A Errors

The key way to bypass the IFERROR issue, particularly for lookup formulas, is to use the IF ISNA combination formula.  The syntax is fairly simple and it was the method people generally used before the IFERROR formula was available.

= IF ( ISNA ( original formula ), value_if_error, original formula )

If we were to apply the IF ISNA approach to the same formula in the prior examples, it would look like this:

IFERROR_04a

And you can see, the formula is now pulling the correct value for San Francisco, indicating that it is in our source data table, but has a divide by zero error.

IFERROR_05

The key benefit of using IF ISNA is that it looks specifically for the #N/A error in Excel.  If and only if that error occurs, then Excel will display your error condition.

However, you’ll also notice that, because you have to input your original formula twice, the formula for IF ISNA is much longer and therefore takes longer to input.  This is likely one of the primary selling point for IFERROR, in that, it takes much less time to input the formula because you don’t have to write your original formula twice.

Conclusion

IFERROR is an efficient way to streamline the error checking process, especially if you’re working on a data set that:

  • You are familiar with and understand all of the potential errors within it
  • The errors you do expect are relatively benign
  • Your main goal is to get a quick, clean initial summary before diving in

However, using IFERROR can cause problems down the road if you have a data set that:

  • Is too large for you to fully understand all the possible errors within it
  • Will evolve over time, potentially incorporating new unexpected errors
  • Your final output requires a high level of precision, void of any mistakes

Overall, my opinion is to avoid using IFERROR whenever possible.  It’s better to be direct about the different types of errors you are expecting.  For example, specifically denote what you want the cell to show if you encounter a divide by zero error (If denominator = 0) or not available error (IF ISNA).  This way you don’t accidentally mask an error that you weren’t expecting.

While IFERROR is definitely efficient and also very intuitive, it’s not always the best formula to use to mask errors because it can clearly cause problems down the line.

{ 3 comments… read them below or add one }

November 29, 2015 at 6:37 pm

Hi Matthew,

Great article about IFERROR. Informative with great examples and screen shots to demonstrate.

Cheers,
Kevin Lehrbass

Reply

June 5, 2016 at 4:03 pm

Thank you for pointing this problem out. There could be errors that we want to trap which are not errors in the data.
The syntax of IFERROR (, ) is replacing
If (ISERROR () = true,, )
because the latter runs the same twice.
The syntax that you suggest means running the same formula twice. This could be a very expensive operation. In order to avoid the situation you point out for, I would use an extra column to verify the values, rather than running the same formula twice.

Reply

December 6, 2016 at 4:08 pm

Great article.
I would suggest that if we are going to use IFERROR, we need to think about the kind of error we were going to trap and use that.

So in your example with the VLOOKUP, we can say IF(COUNTIF(B12:B16,B5)=0,”Not Found”,VLOOKUP(b5,B12:c16,2,FALSE)).

This will preserve the formatting, catch the error that we want to catch, but pass through the DIV/0 error.

Reply

Leave a Comment

{ 1 trackback }

  • November 29, 2015

Previous post:

Next post:


Related pages


horizontal analysis excelblank excel formulainsert check mark in excel 2007excel multiply matrixhistogram creator excelhow to nest if functionshow to use count if function in excellooking for duplicates in excellearning formulas in excelexcel horizontal lookuphow to make a bell curve in excel 2010draw histogram in excelhow to create a stacked bar graph in excelsymbol of tick mark in excelcountif in excel exampleexamples of inventory spreadsheetslimitation of vlookuphistogram templatevba to remove duplicatesbin range excelexcel spreadsheet inventoryremove duplicates from excel columncreate a histogram in excelmacro code in excelhow to use excel vlookup functionms excel row functionh lookup in excelmba behavioral interview questionsnested if function in excelprint to pdf primohow to insert a tick symbol in wordvlookup exampleshow to draw normal distribution curve in excel 2007vlookup vba excelsimple excel macro examplefunctions and formulas in excelhow to use vlookup excel 2013test for normality excelinventory format in excelexcel iserror formulacount excel formulaexcel forecast exampleremove duplicate entries from excelnorminv functionexcel nested if functionsonline histogram graph makerexcel if multiple conditionsascending order on excelgaussian curve excelvlookup formula in excel 2007excel useful shortcutsadvanced excel array formulasvlookup function excel 2013excel vba return arrayexcel if statement examples textfinance excel templatesif not blank in exceladobe acrobat convert pdf to excelexcel vba delete duplicate rowsmba syllabus harvardcombine tables excelremove duplicates from column in excelexcel macro create sheetmicrosoft excel quick tipshow to compare to columns in excelusing vlookup and if function togetherwindings tickhow to do conditional formatting in excel 2013formula for a bell curvebins in exceltick symbol wordfind duplicate entry in excelhlookup examplevlookup example spreadsheet
\n