IF function complexity

  • Thread starter Thread starter Seshadrinathan
  • Start date Start date
S

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
 
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.
 
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
 
Back
Top