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
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

@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)

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

@Yehuda Ordower 

One more variant

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

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

Related Conversations