Excel IF statement with multiple conditions

Question

I'm trying to combine multiple IF statements and I can't get it to work. What it should do is the following:

If X=0 and Y>0, return 10000 OR if X>0 and Y=0, return -10000 if neither do nothing.

I have 2 columns with 7000 rows and I want to calculate the fold change between the 2. I am doing that by

=IF(CA2<1;-1/CA2;CA2)

so that 0.9 turns into -1.1. However if you have a value of 0 you get a #div/0! error. I was hoping to work around by giving single regulated values either a +10000 or -10000.

You just need to include an AND in your IF: =IF(AND(X="0",Y>"0"),"1000","0"),IF(AND(X>"0",Y="0"),"-1000","0") — jimmy8ball
– jimmy8ball, Commented Aug 16, 2017 at 11:26
I'm putting the formula like this: =IF(AND(BW2="0",BX2>"0"),"1000","0"),IF(AND(BW2>"0",BX2="0"),"-1000"‌,"0") where BW2 is a number (but the product of a formule, which is the average) — user53965
– user53965, Commented Aug 16, 2017 at 11:49
=IF(AND(BW2=0;BX2>0);10000;IF(AND(BW2>0;BX2=0);-10000;0)) This works, apparently , need to be ; Probably a setting issue. Thank you very much! — user53965
– user53965, Commented Aug 16, 2017 at 12:00

jimmy8ball · Accepted Answer · 2017-08-16 12:01:39Z

1

Your answer should be to include an AND in your if:

 =IF(AND(A2=0,B2>0),1000,IF(AND(A2>0,B2=0),-1000,0))

answered Aug 16, 2017 at 12:01

jimmy8ball

7566 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel IF statement with multiple conditions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related