IF function complexity

[Seshadrinathan]

I am an advanced user of Excel. I have a complex IF
Function which uses GETPIVOTDATA functions that can
return #NA or #REF values. I want to trap the error and
display 0 if the returned value is one of the above. I
don't want to repeat the same formula in the IF statement.

Currently it is coded like this

IF(ISERROR(<<long formula>>),0,<<long formula>>)

I don't want Excel to re-evaluate the same formula if the
formula returns a proper value.

Any help would be greatly appreciated

Thanks
Sesh

 

[Harald Staff]

Hi Sesh

Excel provides no such technique, so you have to call it twice. Many MVPs
has suggested this functionality to the Excel development team, so we hope
to see it in a future version.

 

[Aladin Akyurek]

You can use SETV/GETV from the morefunc.xll add-in,
downloadable at: http://longre.free.fr/english/index.html and use:

=IF(ISERROR(SETV(formula-body)),0,GETV())

 

[Charles Williams]

Hi Sesh,

One way is to use an additional cell: put the long formula in the additional
cell (say a10000) then use
IF(ISERROR(a10000),0,a10000)

The overhead of an additional cell is almost certainly less than the
overhead of storing and evaluating the long formula twice.
You can hide the additional cell if you want.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm