How to do =IF(ISERROR(long formula),0,value) efficiently?

[AlphaQuant]

Hi

I want a way to test a long formula to see if it is an error. If yes then return zero else return the value of the formula. The key is not to have to repeat the formula as it might be extremely long and take a relatively long time to computer. Essentially what I want is

=IF(ISERROR(long formula),0,long formula value)

This is a situation I face all the time and everytime I cannot believe that Excel doesn't have a built-in function for this. There are of course workarounds but each has its problems. Some workarounds I can think of are:
(i) Placing formula in a separate cell. You can then just test the cell value against the error condition. PROBLEM: I can't apply this to large tables of numbers.

(ii) Repeating the formula in else condition of if statement. PROBLEM: Cumbersome and doubles the execution time of code when it's really not necessary. Thus also unsuitable for applying to large spreadsheets.

(iii) Writing custom vba user defined function. This is probably what I'll have to do, however; PROBLEM: I'm told UDFs are also slow so this is also not ideal for large workbooks. Also you either have to rewrite the function in every workbook you use it in or put it in a standard XLA but then the spreadsheet might not run on colleagues machines who don't have the XLA installed.

Does anyone know of any workarounds using only worksheet functions? Any comments on the issue in general?

[fairwinds]

Hi,

If your long formula returns a number, maybe you could do like this?

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,longformula))

[AlphaQuant]

Thanks, that works.

At first I was a bit confused why you used CHOOSE but it seems this is to trick Excel to return an array because when I tried =LOOKUP(9e307,{0,longformula}) it gave an error.

It works in my case as I have only positive numbers but I suspect the formula will have to be amended if my longformula returned positive and negative values.

This has given me something to work with though, so thank you very much.

[fairwinds]

No you do not need to alter the formula.

It will return the "longformula" result as long as it is a numerical value regardless negative or positive.


But you are right about CHOOSE returning an array, when fed an array constant as first argument.

[Aladin Akyurek]

If you invoke ISERROR very often...

=IF(ISNUMBER(SETV(Calculation)),GETV(),0)

would also have a significant impact on speed.

SETV/GETV are supplied by Longre's morefunc add-in.