Combining ABS and ISERROR

[rlc]

I need to modify the statement below to include ISERROR so that it will also display N/A for a variance percentage calculator. Is it possible to insert in this string. Any suggestions are welcomed.


=IF(AI34<>0,IF(ABS((Y34-AI34)/AI34*100)>1000,"N/A",((Y34-AI34)/AI34*100)),"N/A")

 

[Expiry]

I need to modify the statement below to include ISERROR so that it will also display N/A for a variance percentage calculator. Is it possible to insert in this string. Any suggestions are welcomed.


=IF(AI34<>0,IF(ABS((Y34-AI34)/AI34*100)>1000,"N/A",((Y34-AI34)/AI34*100)),"N/A")

I don't fully understand what else you're trying to add to this.

 

[rlc]

This formula is based on percentages, it works fine when there are values in each bucket, but when there is no value in one the formula displays #value. I found older formulas that used ISERROR. I would like to use that function to use produce N/A. I hope that is clearer.

 

[Aladin Akyurek]

I need to modify the statement below to include ISERROR so that it will also display N/A for a variance percentage calculator. Is it possible to insert in this string. Any suggestions are welcomed.


=IF(AI34<>0,IF(ABS((Y34-AI34)/AI34*100)>1000,"N/A",((Y34-AI34)/AI34*100)),"N/A")

Maybe...

=IF(ISNUMBER(AI34),
   IF(A134 > 0, 
     IF(ABS((Y34-AI34)/AI34*100) > 1000,
       "N/A",
       ABS((Y34-AI34)/AI34*100)),
     "N/A"),
    "N/A")

 

[Scott Huish]

Perhaps:

=IF(N(A134),IF(ABS((Y34-AI34)/AI34*100)<= 1000,((Y34-AI34)/AI34*100),"N/A"),"N/A")