IfError Explained

Microsoft training delivered by certified trainers, and a microsoft partner training company
excel training Excel IFError Statements Explained
logica
guides
Home
       
The IFERROR introduced in Excel 2007 helps Simplfy Complicated Formula

Before the introduction of the IFERROR formula in Excel 2007, if you wanted to hide errors displayed by some formulas you had to employ a combination of IF and the ISNA formulas, which resulted in Excel having to do the calculation twice.  Once to establish whether the result was an error, and again if the result wasn’t an error.
Here is an example of two formulas the first using the old method and the second on using the new function
IFERROR

=IF(ISERROR(VLOOKUP(C4,B11:D18,3,FALSE)),"invalid code",VLOOKUP(C4,B11:D18,3,FALSE))
=IFERROR(VLOOKUP(C4,B11:D18,3,FALSE),"invalid code")

As you can see from the above statements the introduction of the iferror functions makes writing formulas easier for us all, it also has the advantage of speeding up the transaction process.

What can we use it for?
The IFERROR formula can be used as a ‘wrapper’ to hide various errors such as ; #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, and one of the most common being VLOOKUP’s #N/A error.
In this tutorial we’re going to look at using the IFERROR to solve VLOOKUP’s #N/A error.
We’ll cover why we might want Excel to hide this error, and how we can tell Excel to display something more elegant in its place.


VLOOKUP Function revisited :

The Exact Match VLOOKUP:
VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)

The Sorted List VLOOKUP:
VLOOKUP(find this value, in that table, return the value in column x of the table)
Excel will return a #N/A error if it can’t find the value it’s looking for in the table
.

Problems caused with the #N/A Error
A spreadsheet littered with #N/A’s can be unsightly when you’re presenting the data in a report format.
#N/A’s presence in any cell of a row or column will prevent you doing calculations.  The result of a SUM on a row or column with #N/A’s will be #N/A. 
#N/A’s are not very informative.  Something like ‘Not Found’ or ‘Data Missing’, or ‘Invalid Data’ would be more helpful.


Just like you can’t calculate a column containing cells with #N/A’s, you can’t apply any other formulas to them either.  It would be more helpful for Excel to enter a 0 (zero) which won’t break any dependant formulas (other than dividing by 0).

How to Remove #N/A
By enclosing your VLOOKUP formula in an IFERROR formula you can instruct Excel to hide the error, or replace  (text, a number, or nothing) in its place.
using our VLOOKUP formula above, and enclosing it in the IFERROR formula, in English our new formula would read:


=IFERROR(VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly), if you can’t find it put the word ‘Missing’ in the cell)
When we enter our formula in Excel, and apply it to the example we used for our VLOOKUP Exact Match example it would look like this:


=IFERROR(VLOOKUP(A2,$G$2:$H$8,2,FALSE),”Missing”)

You can see in the spreadsheet below that ‘Domonic” is no longer in our Commission Rates table
and the IFERROR formula is telling Excel to put the word ‘Missing’ in the cell.

 

iferr1

If we wanted Excel to put a number, say 0 in the cell instead of a word our formula would look like this.
=IFERROR(VLOOKUP(A2,$G$2:$H$8,2,FALSE),0) see image below



iferr2


Notice the 0 does not have inverted commas “ “ surrounding it like the text ‘Missing’ .
The rule is in Excel to enter text you need to surround it in inverted commas, but numbers you just enter them without the inverted commas.


To enter nothing, your formula would read: =IFERROR(VLOOKUP(A2,$G$2:$H$8,2,FALSE),””)

To enter a dash – your formula would read: =IFERROR(VLOOKUP(A2,$G$2:$H$8,2,FALSE),”-”)

Use the IFERROR for Divide by Zero

The second  most common error is #DIV/0!  Let’s look at how we would hide this error by enclosing it in IFERROR.

if we had a calculation that was =1000/0 the result would be #DIV/0!.  To hide this we can enclose our formula in the IFERROR like this:

=IFERROR(1000/0,”Error”) and instead of Excel displaying #DIV/0! it would display ‘Error’

Or if we wanted it to display 0 we’d enter the formula like this:


=IFERROR(1000/0,0)

 

Finally - Cell Error Print Options

If you want to keep the errors in the spreadsheet and only hide them when printing?  it’s useful to know where the errors are located so you can correct those that are not expected, but if you print reports regularly you probably don’t want the errors shown.

There’s a simple print setting that will allow you to define how errors are displayed when printing.  You can choose to either enter a — in the place of any errors, or leave the cell blank.

On the Page Setup on the Sheet tab choose how you want cell errors displayed from the drop down list.



ifeer3

Excel can be fun try out some of its features Detailed on this Site

 

Mike Barrett (MCT) Senior Training Advisor