IFERROR - a new(ish) way to handle errors

Oftentimes I see formulae that attempt to deal with errors, such as division by zero or failed VLOOKUPs.  Something like this for example:

=IF(ISERROR([something]),[something else],[something])

I never liked this because Excel actually has to do the [something] bit twice. Worse yet you may get one right and one wrong, e.g. due to updating one but not the other. Excel 2007 introduced a new function that addresses these points, called IFERROR. The following formula does exactly the same as the above but more robustly:

=IFERROR([something],[something else])

If [something] returns a valid answer then IFERROR returns it! If it's an error then IFERROR will return the result of the [something else] calculation instead. Notice how much shorter the formula can be, especially if [something] is quite long. Plus you only have to type each part once so there's less risk of accidents.

As a final point do beware of sending the spreadsheet to anyone using an earlier version of Excel. If you do then this function won't be recognised and you'll just get errors! In such cases you may need to rewrite these formulae using the original format shown above. Alternatively you can easily recreate the IFERROR function in VBA instead - just paste the following into a module:

Public Function IfError(Value, OtherValue)
'Do not use this if saving in Excel 2007 format or later!
    If IsError(Value) Then IfError = OtherValue Else IfError = Value
End Function