SOLVED
Home

IF Statement for Multiple conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-729304%22%20slang%3D%22en-US%22%3EIF%20Statement%20for%20Multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729304%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20-%20Trying%20to%20write%20a%20statement%20that%20will%20do%20the%20following%3C%2FP%3E%3CP%3EIf%20%22Closed%22%20then%20return%200%3C%2FP%3E%3CP%3EIf%20%22P%22%20AND%20X%20%26lt%3B%20Y%2C%20then%20Y-X%2C%20otherwise%200%3C%2FP%3E%3CP%3EIf%20%22C%22%20AND%20X%26gt%3B%3DY%2C%20then%20X-Y%2C%20otherwise%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20the%20statement%20I%20wrote%20and%20seems%20like%20it%20works%20if%20I%20have%20%22Closed%22%20or%20%22C%22%20but%20not%20%22P%22%3C%2FP%3E%3CP%3E%3DIF(AND(A1%3D%22Closed%22%2C0)%2CIF(AND(H1%3D%22P%22%2CN1%3CO1%3E%3DY1)%2CN1-O1%2C0))%3C%2FO1%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-729304%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729337%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F240888%22%20target%3D%22_blank%22%3E%40Yehuda%20Ordower%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELetting%20'State'%20be%20%5B%22Open%22%2F%22Closed%22%5D%20and%20'PC'%20be%20%5B%22P%22%2C%22C%22%5D%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20(State%3D%22Open%22)%20*%20(%20(PC%3D%22P%22)*(Y%26gt%3BX)*(Y-X)%20%2B%20(PC%3D%22C%22)*(X%26gt%3BY)*(X-Y)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhich%20seems%20to%20reduce%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20(State%3D%22Open%22)%20*%20(%201%20-%20(PC%3D%22P%22)%20-%20(Y%26gt%3BX)%20)%20*%20(X-Y)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729393%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729393%22%20slang%3D%22en-US%22%3ETry%20this%20Boolean%20formula%3A%3CBR%20%2F%3E%3D(A1%26lt%3B%26gt%3B%22Closed%22)*%3CBR%20%2F%3E(((H1%3D%22P%22)*(N1%3CO1%3E%3C%2FO1%3E((H1%3D%22C%22)*(N1%26gt%3B%3DY1)*(N1-O1)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729688%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F240888%22%20target%3D%22_blank%22%3E%40Yehuda%20Ordower%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%3C%2FP%3E%0A%3CPRE%3E%3D(A1%26lt%3B%26gt%3B%22Closed%22)*(X1-Y1)*CHOOSE((X1%26lt%3BY1)%2B1%2C(H1%3D%22C%22)%2C-(H1%3D%22P%22))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735397%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735397%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%20-%20thank%20you%20for%20your%20super%20fast%20response%20and%20assistance%20with%20my%20Excel%20queries%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Yehuda Ordower
Occasional Contributor

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

@Yehuda Ordower 

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

@Yehuda Ordower 

One more variant

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

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

Related Conversations
Excel If Functions
Mfouad2255 in Excel on
10 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies