Solved In excel, I need a formula in C1 that does the following:

March 7, 2012 at 11:43:48
Specs: Windows XP
If A1>0 and B1>0 then set C1 to A1
or A1<0 and B1<0 then set C1 to 0
or A1>0 and B1<0 then set C1 to "Not Good"
or A1<0 and B1>0 then set C1 to 0

Hopefully someone can help me out on this one!
Example:
A1 B1 C1
1.00 1.00 1.00
-1.00 -1.00 0.00
1.00 -1.00 Not Good
-1.00 1.00 0.00


See More: In excel, I need a formula in C1 that does the following:

Report •


#1
March 7, 2012 at 12:20:15
Try:

=IF(AND(A1>0,B1>0),A1,IF(AND(A1<0,B1<0),0,IF(AND(A1>0,B1<0),"Not Good",IF(AND(A1<0,B1>0),0,""))))

MIKE

http://www.skeptic.com/


Report •

#2
March 7, 2012 at 12:41:13
✔ Best Answer
What happens if either A1 and/or B1 equal zero?

If that can never happen, then this shorter version might work for you:

=IF(AND(A1>0,B1>0),A1,IF(AND(A1<0,B1<>0),0,"Not Good"))

By the way...

Mike's suggestion can be shortened since these 2 clauses:

IF(AND(A1<0,B1<0),0
IF(AND(A1<0,B1>0),0

can be combined as:

IF(AND(A1<0,B1<>0),0

If B1 is not equal to zero, then it must greater than or less than 0, and since you want 0 in either case, you only need to check it once.

I think...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 8, 2012 at 09:45:57
Awesome! Thanks, it worked perfect.

Report •

Related Solutions

#4
March 8, 2012 at 09:47:06
Awesome, it worked perfect. Thanks!

Report •


Ask Question