MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Preventing Formula Errors


October 05, 2017 - by Bill Jelen

Preventing Formula Errors

Preventing formula errors in Excel using IFERROR or IFNA. These are better than the old ISERROR ISERR and ISNA. Learn more here.

Formula errors can be common. If you have a data set with hundreds of records, a divide-by-Zero or a #N/A are bound to pop up now and then.

In the past, preventing errors required herculean efforts. Nod your head knowingly if you’ve ever knocked out =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Besides being really long to type, that solution requires Excel to do twice as many VLOOKUPs. First, you do a VLOOKUP to see if the VLOOKUP is going to produce an error. Then you do the same VLOOKUP again to get the non-error result.

Excel 2010 introduced the greatly improved =IFERROR(Formula,Value If Error). I know that IFERROR sounds like the old old ISERROR, ISERR, ISNA functions, but it is completely different.

This is a brilliant function: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). If you have 1,000 VLOOKUPs and only 5 return #N/A, then the 995 that worked only require a single VLOOKUP. It is only the 5 that returned #N/A that need to move on to the second argument of IFERROR.

Oddly, Excel 2013 added the IFNA() function. It is just like IFERROR but only looks for #N/A errors. One might imagine a strange situation where the value in the lookup table is found, but the resulting answer is a division by 0. If you wanted to preserve the divide-by-zero error for some reason, then IFNA() will do this.

#DIV/0!
#DIV/0!

Of course, the person who built the lookup table should have used IFERROR to prevent the division by zero in the first place. In the figure below, the “n.m.” is a former manager’s code for “not meaningful.”

IFERROR Function
IFERROR Function

Thanks to Justin Fishman, Stephen Gilmer, and Excel by Joe.

Watch Video

  • In the old days, you would use =IF(ISNA(Formula),0,Formula)
  • Starting in Excel 2010, =IFERROR(Formula,0)
  • But IFERROR treats DIV/0 errors the same was a #N/A! errors
  • Starting in Excel 2013, use the =IFNA(Formula,0) to detect only #N/A errors
  • Thanks to Justin Fishman, Stephen Gilmer, and Excel by Joe.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2042 if error and if na i'll be
  • podcasting all my tips from this book
  • click the I in the top right hand corner
  • to get to the entire playlist alright
  • let's go back to the old days Excel 2007
  • or before if you needed to prevent the
  • na from a vlookup formula such as this
  • one we used to do this crazy thing scoop
  • all of the characters of the four mix up
  • for the equal sign out ctrl-c to put it
  • on the clipboard equal if open
  • parenthesis is na open parenthesis press
  • the end key to get to the end if it's na
  • then we say not found comma otherwise we
  • do the vlookup right I paste it in there
  • and look how long that formula is ctrl
  • enter add a parenthesis right there
  • control enter all right look at sweet
  • all right but the problem is while it
  • solves the problem of the n/a every
  • single formula is doing the vlookup toys
  • it doesn't wants to say hey is this an
  • error oh no it's not an error let's go
  • do it again so it takes twice as long to
  • do all of these vlookups in Excel 2010
  • they give us the awesome awesome formula
  • of if error now I know that sounds like
  • what we had before is error or is air
  • but this is if error in the way that it
  • works take any formula that might return
  • an error and then you say equal if error
  • there's the formula comma what to do if
  • it's not if it's an error so not found
  • all right now
  • beautiful thing about this is let's say
  • you had a thousand vlookups to do and
  • 980 of them work for the 980 it just
  • does the vlookup it's not an error it
  • returns the answer it never goes on to
  • do the second vlookup that's the beauty
  • about affair except if error came along
  • in Excel 2010 but of course the one
  • really stupid problem here is that the
  • table itself is returning an error right
  • someone had zero quantity revenue /
  • quantity and so we're getting a division
  • by zero error there and that error is
  • also going to be detected as an error by
  • if error all right and it's going to
  • look like the mr excel is knife out he
  • is fat it's just that whoever built this
  • table didn't do a good job building this
  • table all right so choice number one
  • excel 2013 or newer switch over to the
  • function they added in 2013 that doesn't
  • look for all layers it only looks for na
  • control enter and now we'll get the not
  • found for Excel is fun but Mr Excel is
  • returning the division by zero error
  • really though what should we what we
  • should be doing is out here in this
  • formula we should be handling this for
  • me to prevent that division by zero in
  • the first place so equal if error this
  • works for all kinds of things press the
  • end key to get to the end comma and then
  • what to do I would always put a zero
  • here as the average price I had a
  • manager wants to Susanna that's not
  • mathematically correct you have to put
  • in quotes and period M period for not
  • meaningful exactly like that it's crazy
  • how long my manager just drove me crazy
  • with their crazy request so let's copy
  • that down a special formulas I'll ESF
  • now we get a not meaningful error here
  • the not found is found by the if error
  • and the not meaningful is actually the
  • result of the vlookup all right so a
  • couple different ways to go probably the
  • safe thing to do here is to use if na
  • provided you have Excel 2013 and
  • everyone you're sharing your workbook
  • with has excel 2013 this book plus a
  • whole bunch of others are in this book
  • is dripping with spicy tips
  • 200 pages easy to read full color
  • awesome awesome book check it out click
  • the eye on the top right hand corner you
  • can buy the book alright episode recap
  • back in the old days we'd use a long
  • format equal if open paren is na open
  • paren the formula comma 0 otherwise the
  • formula the formula was calculated twice
  • which is horrible for vlookups starting
  • Excel 2010 equal if error just put the
  • forms of once comma what to do if it's
  • an error if error though treats division
  • by zero errors the same as not
  • applicable errors are not available
  • errors so starting Excel 2013the if and
  • a function works just like if there but
  • it will detect only the pound na errors
  • this tip by the way suggested by readers
  • Justin Fishman Steve and gilmer and
  • Excel bhai geo thanks to them for
  • suggesting this and thanks to you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2042.xlsm

Title Photo: sign/post/BD16DF06-48F2-4D22-BB67 / Pixabay


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.