IFERROR
Thursday, 3rd July, 2008 by SimonDo you find yourself writing:
IF(ISERROR(some big thing), 0, some big thing)?
One of the more useful features of Excel 2007 was support for IFERROR(some big thing, 0). The ability to wrap up errors and provide a default value. As well as making your functions easier to cope with this also halves the calc time as ’some big thing’ only calculates once.
Bullen Bovey and Green used IFERROR as their example of xlls in PED. So if you have that you should have the iferror xll that works in 97-2003.
Personally I think IFERROR is a bit too broad brush because it also wraps #REF! errors which could be a sign someone just killed you spreadsheet. But thats not the point of this post, we’re talking XLM.
You could write IFERROR in VBA and suffer that calling overhead, But what if you don’t have PED, or time for VBA?
TaDaa
XLM
an XLM IFERROR is as fast at the xll version (nearly) and can be included with every workbook that needs it. And the code is simple:
Right click the tabs and insert a macro sheet.
put this in from A1 downwards:
- =RESULT(7)
- =ARGUMENT(”PossError”,31)
- =ARGUMENT(”Default”,31)
- =PossError
- =IF(ISERROR(A4),RETURN(Default),RETURN(A4)
Click on A1, Insert>>Names>Define, call it IFERROR, make sure to select function in the bottom right section of the dialog and click OK.
Back in a worksheet enter =IFERROR(someref, some other ref)
And job done. Charles Williams suggests that hiding the macro sheet can boost performance.
If you compare performance of a couple of thousand cells worth of these formulas with a VBA version you will be impressed, especially if you prefer auto calc (I do).
To explain the above macro:
1 - a function should have a return type, this says what it is, details in macrofun.hlp
2 & 3- each argument needs a type and a temporary ‘variable’ to holds its value
4 - take whatever was passed in in the first argument (PossError) and stick it in a cell
5 - Do your IF(ISERROR stuff on that cell, so it only needs calculating once.
Its all very close to normal worksheet Excel, once you get over some of the arcane syntax. But in fairness thats Excel too - who’d know that SUBTOTAL(1,..) means average.
Have a go and let us know how you get on. If anyone has any suggestions or improvements or alternatives for the above let me know. I did try a few other ways but this seemed the best performance where PossError was a chunky formula. And its easy to explain as its similar to the the worksheet formula way.
cheers
Simon
