• 544K Members
• 5,545 Online
• 648K Conversations
SOLVED

IF Statement for Multiple conditions

Highlighted
Occasional Contributor

IF Statement for Multiple conditions

HI - Trying to write a statement that will do the following

If "Closed" then return 0

If "P" AND X < Y, then Y-X, otherwise 0

If "C" AND X>=Y, then X-Y, otherwise 0

Below is the statement I wrote and seems like it works if I have "Closed" or "C" but not "P"

=IF(AND(A1="Closed",0),IF(AND(H1="P",N1<O1),O1-N1,0),IF(AND(H1="C",N1>=Y1),N1-O1,0))

Any suggestions?  Thanks

4 Replies
Highlighted

Re: IF Statement for Multiple conditions

Letting 'State' be ["Open"/"Closed"] and 'PC' be ["P","C"]

= (State="Open") * ( (PC="P")*(Y>X)*(Y-X) + (PC="C")*(X>Y)*(X-Y) )

which seems to reduce to

= (State="Open") * ( 1 - (PC="P") - (Y>X) ) * (X-Y)

Highlighted

Re: IF Statement for Multiple conditions

Try this Boolean formula:
=(A1<>"Closed")*
(((H1="P")*(N1<O1)*(O1-N1))+
((H1="C")*(N1>=Y1)*(N1-O1)))
Highlighted
Solution

Re: IF Statement for Multiple conditions

One more variant

`=(A1<>"Closed")*(X1-Y1)*CHOOSE((X1<Y1)+1,(H1="C"),-(H1="P"))`
Highlighted

Re: IF Statement for Multiple conditions

Sergei - thank you for your super fast response and assistance with my Excel queries

Related Conversations
Excel If Functions