if(iserror()) question
in [Functions]
|
Prev: When entering formula it does not show the value but the formula
Next: can't save changes to workbook; ready only
From: Mike B. Mike on 19 Jan 2010 10:19 Hello, I'm trying to design a formula that returns a (blank) if a formula returns an error OR a zero. The formula is of this type: =if(or(iserror(getpivotdata(.....),getpivotdata(.....)=0),"",getpivotdata(.....)) This formula continually returns #REF for errors but works properly on the "zero condition". I can also get it to work properly for the "iserror condition" but return 0 for the "zero condition". I just can't get it to work properly on both conditions. Is this a limitation in Excel or can someone point me in the right direction. Thanks.
From: Gary''s Student on 19 Jan 2010 10:28 Here is a typical example. The original is: =A1/A2 the modified is: =IF(ISERROR(A1/A2),"",IF(A1/A2=0,"",A1/A2)) -- Gary''s Student - gsnu200909 "Mike B." wrote: > Hello, > I'm trying to design a formula that returns a (blank) if a formula returns > an error OR a zero. The formula is of this type: > > =if(or(iserror(getpivotdata(.....),getpivotdata(.....)=0),"",getpivotdata(.....)) > > This formula continually returns #REF for errors but works properly on the > "zero condition". I can also get it to work properly for the "iserror > condition" but return 0 for the "zero condition". I just can't get it to > work properly on both conditions. Is this a limitation in Excel or can > someone point me in the right direction. > > Thanks. > >
From: Mike B. on 19 Jan 2010 10:44
Thanks so much for the fast response. Worked perfectly. "Gary''s Student" wrote: > Here is a typical example. The original is: > > =A1/A2 > > the modified is: > > =IF(ISERROR(A1/A2),"",IF(A1/A2=0,"",A1/A2)) > > -- > Gary''s Student - gsnu200909 > > > "Mike B." wrote: > > > Hello, > > I'm trying to design a formula that returns a (blank) if a formula returns > > an error OR a zero. The formula is of this type: > > > > =if(or(iserror(getpivotdata(.....),getpivotdata(.....)=0),"",getpivotdata(.....)) > > > > This formula continually returns #REF for errors but works properly on the > > "zero condition". I can also get it to work properly for the "iserror > > condition" but return 0 for the "zero condition". I just can't get it to > > work properly on both conditions. Is this a limitation in Excel or can > > someone point me in the right direction. > > > > Thanks. > > > > |