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 4043 times – 7 KB

you’re a lifesaver!

thank you so much!!

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.

Dude,

Great job.

It was really a wonderful time saver.

Thanks a lot for the “little” modules. Really useful.

Cheers

MM

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

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.

Rick, check out my latest blog for something that should work for you..

https://www.professionalexcel.com/2013/04/replace-ifiserror-with-value_if_false-argument/

Thank you sooo much!!!

Man…. I owe you a beer honestly……… you are the best!!!!!!!

Most Helpful. Huge time saver. Thank you for building & sharing!!

Thanks a million. saved me loads of time!

Just what I needed to make my Excel 2010 file work correctly in Open Office. Excellent time-saver. Thanks!

Pin my tail and call me a doykne, that really helped.

This is great! Thanks a ton

Pingback: Complex Transpose « ProfessionalExcel.com