ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

23Apr/136

Replace IF(ISERROR()) with value_if_false argument

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

A reader of my blog was after some code to do something a little different to a previous post which looked at replacing the Excel 2007 IFERROR function with IF and ISERROR.

The slight twist on this was when you wanted to get rid of the error trapping (IF and ISERROR) and just have the original formula, warts (errors) and all!

In the VBA code below, I have developed a handy procedure (ReplaceIFISERRORCellFormulas) that will convert a range of selected cells that contain IF(ISERROR()) formulas to instead display the formula used in the 'value_if_false' argument of the original IF(ISERROR()) function.

13Jul/113

Complex Transpose

I’ve called this tip ‘Complex Transpose’, which hopefully describes what I’m about to show you. Essentially, it’s about transposing a two column data set of related items into a multi-row and column data set.

The key technique employed utilises Array formulas, which I won’t go into detail about, mainly because plenty before me have (see links below). The quick 'how-to' is that you need to hold down the CTRL & SHIFT keys when pressing ENTER to commit the formula, instead of just hitting ENTER. Their other common name is a ‘CSE formula’ (Control, Shift, Enter). When committed, the formula is surrounded by curly {brackets}.

21Jun/1111

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