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

Share this content
1

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...

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

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)