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.