Combine two different if statements

[Anthos]

Hi boys & girls...I want to use a single formula to test two conditions.

ColA ColB ColC Wanted result
-100 -150 -33% 33%

To get the wanted result I use this formula

=IF(B1<-1;((A1-B1)/B1*-1);(A1-B1)/B1)

But I also want to check if column B has a 0 or #Div/0! and return 0
so I use this formula

=IF(OR(B1=0;B1="#DIV/0!");0;(A1-B1)/B1)

Is there a way to combine the two formulae?

Thanks,
Anthos

 

[SteveO59L]

Try using

=If(iserror(.......


to check for your #Div/0! entries

 

[erik.van.geit]

Hi,

try this

=IF(B1=0,0,IF(B1<-1,-(A1-B1)/B1,(A1-B1)/B1))

kind regards,
Erik

 

[erik.van.geit]

Hi, again,

I think you can use this to shorten the formula:

=IF(B1=0,0,SIGN(B1+1)*(A1-B1)/B1)

greetings,
Erik