complex if statement.. need help.. thankyou

[cbs81]

Col A Col B Col C
5% above or below??
300.00 350.00
350.00 300.00
300.00 0
0 350.00


I have three columns above... A, B, C with the following rows as above

column c needs to identify whether the difference of Col A and B exceeds column B by 5% and identify whether it is below by 5% or above by 5%.... This is taking me ages because col b could be 0 and have a corresponding amount in column a, in which case I want col C to say "below by 5%" same applies to column a which could be 0 and have a corresponding amount in column b, in which I would like column C to output "above by 5%". If the difference of A and B is less than 5% than I want there to be nothing displayed in column c...

the formula i have so far is as follows but this does not work:

if(isnumber(and(a7,b7),if(A7-B7)/B7)>=0.05,"above 5%",if(A7-B7)/B7)<=-0.05,"below 5%")),IF(AND(A7>0,B7=0),"5% above",IF(AND(B7>0,A7=0),"5% below",)


I need some help in the best way of solving this problem...

Thanks heaps and I look forward for someones professional help

[Bob Phillips]

=IF(AND(A1=0,B1<>0)," 5% above",IF(AND(A1<>0,B1=0),"5% below",IF(ABS((B1/A1)-1)>=0.05,"5% above","")))

[cbs81]

Hi and thankyou for your response, the above does work, however if there is a zero in both column A and B, column C outputs a DIV/0! error... how can we fix this..??

[Bob Phillips]

=IF(AND(A1=0,B1=0),"",IF(AND(A1=0,B1<>0)," 5% above",IF(AND(A1<>0,B1=0),"5% below",IF(ABS((B1/A1)-1)>=0.05,"5% above",""))))

[cbs81]

i got it.... here is the working formula:

=IF(COUNT(A7,B7)=2,IF(B7,IF(ABS((A7-B7)/B7)>=0.05,CHOOSE(SIGN((A7-B7)/B7)+2,"below 5%","","above 5%"),""),"above 5%"),IF(AND(A7=0,B7<>0)," above 5%",IF(AND(A7<>0,B7=0),"below 5%",IF(AND(A7=0,B7=0),""))))