How to compare 2 columns using multiple criteria

[csh8428]

I apologize for the first 2 replies I completely re-did my post.

I have 2 columns(A and B) both filled with numbers with an undefined number of rows as it will be different every time.
Column A is labeled "/6" and column B is "/7 ".

I need a way to add formulas in Column C based on these criteria

IF /7 > /6 and both are positive. Then do this in column C: Subtract /6 from /7
IF /7 < /6 and both are negative. Then do this in column C: subtract /7 from /6
IF /7 is positive and /6 is negative. Then do this in column C: subtract /6 from /7
IF /7 is negative Then do this in column C: =/7

I was thinking of using a CASE statement, but can't figure how to code it.

Thanks for any help!,

Regards,

Craig

[carden2]

that code below isn't quite right. I am not sure how to make it work right. The first code was really close.

[Bishonen]

does this work? don't have excel open to test it thus its name ^^

Please Login or Register  to view this content.

pretty sure that even if it works, its not the best way to go around this problem.

[csh8428]

Cardon and Bishon.. I apologize, but I modified my request heavily so I don't think your responses would fit it any longer.

[FDibbins]

ok this is messy, and i dont have your data to test it, but try this in col C...
=IF(AND(B2>A2,A2>0),B2-A2,IF(AND(B2<A2,A2<0),A2=B2,IF(AND(B2>0,A2<0),B2-A2,IF(B2<0,B2,""))))

[csh8428]

Fdibbins,

I get an error when I used the code:

ok this is messy, and i dont have your data to test it, but try this in col C...
=IF(AND(B2>A2,A2>0),B2-A2,IF(AND(B2<A2,A2<0),A2=B2,IF(AND(B2>0,A2<0),B2-A2,IF(B2<0,B2,""))))

The error is :
Run-time error '1004'
Applicaiton-defined or object-defined error

Thanks,

Craig

[csh8428]

I think something like this might work, but I can only figure out how to code it for one cell(C1), and I"m not even sure if my syntax is right because I can't test it.

Please Login or Register  to view this content.

Could someone please correct my syntax and how would I get the macro to cycle through and go through the entire spreadsheet putting the correct formula in each successive cell in column C??

Thanks,

[FDibbins]

my sugesstion was meant to be for a formula, not for use in VBA. your initial post asked for a formula...

Please Login or Register  to view this content.