cbs81

01-29-2007, 10:37 PM

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

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