Preventing Formula Errors
October 05, 2017 - by Bill Jelen
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.
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.”
Thanks to Justin Fishman, Stephen Gilmer, and Excel by Joe.
- In the old days, you would use
- Starting in Excel 2010,
- 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.
Learn Excel from MrExcel podcast, episode 2042 – IFERROR and IFNA!
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 #N/A! from a VLOOKUP formula such as this one, we used to do this crazy thing. Scoop all of the characters of VLOOKUP mix up except for the =, Ctrl+C to put it on the clipboard, =IF(ISNA(, press the End key to get to the end, if it's #N/A!, 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 ) right there, Ctrl+Enter, alright look, that’s sweet. Alright, but the problem is, while it solves the problem of the #N/A! every single formula is doing the VOOKUP twice. It doesn't wants us 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 IFERROR!
Now I know that sounds like what we had before, ISERROR or ISERR, but this is IFERROR. And the way that it works, take any formula that might return an error, and then you say =IFERROR, there's the formula, comma, what to do if it's an error, so “Not Found”. Alright 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 IFERROR. Excel’s IFERROR 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 0 quantity, Revenue/Quantity, and so we're getting a #DIV/0! error there, and that error is also going to be detected as an error by IFERROR. Alright, and it's going to look like the MrExcel is not found, he is found, it's just that whoever built this table didn't do a good job building this table.
Alright so, choice #1, Excel 2013 or newer, switch over to the function they added in 2013 that doesn't look for all errors, t only looks for #N/A! Ctrl+Enter, and now we'll get the Not Found for ExcelIsFun, but MrExcel is returning the #DIV/0! error. Really though, what we should be doing, is out here in this formula, we should be handling this formula to prevent that division by zero in the first place. So =IFERROR, 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 once, Susanna(?), “That's not mathematically correct, you have to put in “n.m.” for not meaningful.” Exactly like that, it's crazy how long my manager just drove me crazy with their crazy request, sso, let's copy that down, Paste Special Formulas, Alt E S F. Now we get a “not meaningful” error here, the “Not Found” is found by the IFERROR, and the “not meaningful” is actually the result of the VLOOKUP. Alright, so a couple different ways to go, probably the safe thing to do here is to use IFNA, 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 “I” 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 =IF(ISNA(the formula,0, otherwise the formula, the formula was calculated twice, which is horrible for VLOOKUPs. Starting in Excel 2010, =IFERROR, just put the formula once, comma, what to do if it's an error. IFERROR, though, treats #DIV/0! errors the same as #N/A! errors, so starting Excel 2013 the IFNA function works just like IFERROR, but it will detect only the #N/A! errors.
This tip by the way, suggested by readers Justin Fishman, Stephen Gilmer, and Excel by Joe. Thanks to them for suggesting this, and thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Download the sample file here: Podcast2042.xlsm
Title Photo: sign/post/BD16DF06-48F2-4D22-BB67 / Pixabay