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

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.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

IFERROR is great but there's more ...

todlad52 | | Permalink

 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