Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Fun with functions: Dealing with errors and the new Excel 2007 IFERROR() function

by
11th May 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst explains how to use the new Excel 2007 function to streamline error-checking in your spreadsheets.

Sometimes a formula in a worksheet will return an error value. Perhaps one of the most common examples occurs when you are calculating one column of values as a percentage of another column and zero values result in #DIV/0 error messages.

If you want to avoid the printing of the error messages you could go to the sheet options tab of the Page Setup dialog and set the 'Cell errors as:' option. However, this will hide any error messages on the sheet and thus possibly cover up vital warnings in other areas of the sheet:


A more specific approach would be to trap the error before it occurs using an IF() function such as:

=IF(C12=0,"",B12/C12)

This gives the opportunity to enter whatever value or warning you require as the 'True' (second) argument.
In this case it's quite easy to predict and prevent the likely error. In other situations you might have to perform the calculation that generates the error before you know whether or not there will be an error. In this example we're using the 'Exact match' variant of VLOOKUP() to return a value from a table:


If there is no match, then VLOOKUP() will return the error value #N/A, but the easiest way to predict whether there is a match would be to use VLOOKUP() or some similar formula that would also return #N/A if there was no match. In order to return a value other than the built-in Excel error you could use one of the Excel information functions:

=IF(ISERROR(VLOOKUP(D28,D22:E26,2,FALSE)),"No match",VLOOKUP(D28,D22:E26,2,FALSE))

Here, we've used ISERROR() to see if the VLOOKUP() function returns an error, and if it does we can choose how to deal with it. If there is no error, we repeat the VLOOKUP() function to produce the required result. There are a couple of potential issues with this approach. Firstly, ISERROR() will return 'True' whatever type of error is generated, so if we'd spelt 'False' incorrectly our IF() statement would still return 'No Match'. There are more specific information functions that deal with particular errors:

ISNA() – for #N/A
ISERR() – for errors other than #N/A
ERROR.TYPE() – returns a number depending on the type of error generated by the cell to which it refers – for example #N/A in cell E28 will cause =ERROR.TYPE(E28) to return 7.

In the case of our VLOOKUP() example, we could use ISNA() to look more specifically for a failure to match. Then, assuming we'd mistyped 'FALSE' in both the VLOOKUP() elements of our formula, we wouldn't get the misleading "No match' but instead, because the first VLOOKUP() generates a different error to #N/A, the second VLOOKUP() would be evaluated, and would produce a #NAME? error.

The second issue with the ISxx() functions is the need to repeat the calculation that we are using, not only does this involve more typing and introduce a possibility of entering the function differently in the error check to the result, but it also means that Excel has to do twice as much work which could be significant for overall calculation time in a big spreadsheet. Excel 2007 addresses this issue with the new IFERROR() logical function. This combines the work of the IF() and the ISERROR() functions and only needs two arguments, the calculation to be checked and the result if it evaluates to an error. So, in our VLOOKUP() function example we could use:
=IFERROR(VLOOKUP(D28,D22:E26,2,FALSE),"Calculation error")

If there is no error, then IFERROR() just returns the value of its first argument, so there is no need to enter the VLOOKUP() function twice. Note that, like ISERROR(), IFERROR() checks for any type of Excel error so can't distinguish the failure to find a match from the incorrect entry of the function – hence the use of the more general message in our example.

The Fun with Excel functions series
1. OFFSET
2. ABS, SUMIF and the array formulae
3. Expenses tracking tutorial for Excel 2007

Also see ExcelZone Compendium: Functions & Formulae

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.

Tags:

Replies (1)

Please login or register to join the discussion.

it's my photo
By todlad52
25th Mar 2010 15:37

IFERROR is great but there's more ...

 You might care to take a look at this page, http://www.excelmaster.co.uk/iferror.htm, which is rather lengthy but it uses IFERROR comprehensively AND it combines IFERROR with custom formatting.

You'll see the power of this combination by looking at the before and after situations:

=IF(ISERROR(D23-D21),"?",if(D21>D23,"?",D23-D21)))

becomes

 

=IFERROR(D23-D21,"?")

 

Take a look at the page to see how a practical example is developed and then pretty well perfected!

 

Duncan

Thanks (0)