Replace IFERROR() with IF(ISERROR())

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula). This meant having to write your formula twice (think of your longest formula!).

In Excel 2007, trapping and handling errors in a formula was made easier with the introduction of the IFERROR function, which removed the need to specify your formula twice, e.g. =IFERROR(MyFormula,ReturnSomethingElse).

With organisations upgrading to Office (Excel) 2007 at different times, compatibility can become an issue. Although installing the Microsoft Office Compatibility Pack will enable users of previous versions of Excel to open an Excel 2007 file, you can’t rely on this being installed. Also, you may want to convert an Excel 2007 file to a previous version for people to use / modify; In Excel 2007, simply ‘saving as’ an older version (e.g. saving an ‘xlsx’ as an ‘xls’ file) will often bring up a load of compatibility warning messages, which aren’t fixed automatically.

Through my need to develop in all versions of Excel, I developed a handy procedure (ReplaceIFERRORCellFormulas) that will convert a range of selected cells that contain IFERROR formulas to instead use a combination of the IF and ISERROR functions. There’s also a procedure to check all Named Ranges (ReplaceIFERRORNamedRangeFormulas), in case you use them to store formulas.

Download “modReplaceIFERROR.bas” modReplaceIFERROR.bas – Downloaded 4060 times – 7 KB

15 thoughts on “Replace IFERROR() with IF(ISERROR())

  1. One major difference, IFERROR only returns a true value. IF(ISERROR) returns a true, false value which can be more helpful when you want to do something only if no error is present.

  2. Pingback: Replace IF(ISERROR()) with value_if_false argument «

  3. Thank you so much.

    I have a huge Excel sheet where there are a couple of hundred different formulas included in the function if(iserror(someformula),0,someformula).

    Do you know of a macro to remove all if(iserror()) functions and keeping the formulas intact?

    Thank you for your help and advice.

  4. Pingback: Complex Transpose «

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.