SOLVED

Help: IF statement with Multiple AND , OR conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-1071180%22%20slang%3D%22en-US%22%3EHelp%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071180%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20some%20help%20writing%20an%20IF%20statement%20that%20includes%20multiple%20AND%20and%20OR%20conditions.%20See%20the%20sample%20file%20attached.%20I%20want%20to%20write%20an%20IF%20statement%20that%20will%20meet%20all%20of%20the%20conditions%20listed%20in%20the%20sample%20file%20and%20that%20will%20achieve%20the%20results%20listed%20in%20the%20%22decreased%20risk%22%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1071180%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071215%22%20slang%3D%22en-US%22%3ERe%3A%20Help%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20if%20this%20formula%20below%20achieve%20what%20you%20want%3A%3C%2FP%3E%3CPRE%3E%3DIF(A2%3D%22Yes%22%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(A2%3D%22No%22%2CB2%3D%22off%22)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%3CBR%20%2F%3EOR(A2%3D%22Off%22%2CA2%3D%22Null%22%2CA2%3D%22DKNS%22)%2C%3CBR%20%2F%3EOR(B2%26lt%3B%26gt%3B%22Off%22%2CB2%26lt%3B%26gt%3B%22Null%22%2CB2%26lt%3B%26gt%3B%22DKNS%22))%2C%22Yes%22%2C%3CBR%20%2F%3E%22DKNS%22)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20one%20gives%20the%20same%20result%20with%20the%20new%20function%20IFS%3A%3C%2FP%3E%3CPRE%3E%3DIFS(A2%3D%22Yes%22%2C%22Yes%22%2C%3CBR%20%2F%3EAND(A2%3D%22No%22%2CB2%3D%22off%22)%2C%22No%22%2C%3CBR%20%2F%3EAND(%3CBR%20%2F%3EOR(A2%3D%22Off%22%2CA2%3D%22Null%22%2CA2%3D%22DKNS%22)%2C%3CBR%20%2F%3EOR(B2%26lt%3B%26gt%3B%22Off%22%2CB2%26lt%3B%26gt%3B%22Null%22%2CB2%26lt%3B%26gt%3B%22DKNS%22))%2C%22Yes%22%2C%3CBR%20%2F%3ETRUE%2C%22DKNS%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071311%22%20slang%3D%22en-US%22%3ERe%3A%20Help%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20reduce%20the%20logic%20and%20this%20is%20what%20my%20formula%20looks%20like%20in%20D2%20of%20the%20attached%20file%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20693px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162260iBB0B47CA933ED0DF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Nested%20IF.PNG%22%20title%3D%22Nested%20IF.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071962%22%20slang%3D%22en-US%22%3ERe%3A%20Help%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071962%22%20slang%3D%22en-US%22%3EThank%20you.%20This%20worked%20perfectly%20for%20what%20I%20am%20trying%20to%20achieve.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071964%22%20slang%3D%22en-US%22%3ERe%3A%20Help%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071964%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20response.%20Unfortunately%20this%20didn't%20provide%20all%20of%20the%20results%20I%20needed%2C%20but%20I%20found%20a%20formula%20that%20did.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071998%22%20slang%3D%22en-US%22%3ERe%3A%20Help%3A%20IF%20statement%20with%20Multiple%20AND%20%2C%20OR%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071998%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20perfectly%20welcome!%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone,

 

I need some help writing an IF statement that includes multiple AND and OR conditions. See the sample file attached. I want to write an IF statement that will meet all of the conditions listed in the sample file and that will achieve the results listed in the "decreased risk" column. 

 

Any suggestions?

5 Replies

@AL789

 

Hi,

 

Please see if this formula below achieve what you want:

=IF(A2="Yes","Yes",
IF(AND(A2="No",B2="off"),"No",
IF(AND(
OR(A2="Off",A2="Null",A2="DKNS"),
OR(B2<>"Off",B2<>"Null",B2<>"DKNS")),"Yes",
"DKNS")))

 

This one gives the same result with the new function IFS:

=IFS(A2="Yes","Yes",
AND(A2="No",B2="off"),"No",
AND(
OR(A2="Off",A2="Null",A2="DKNS"),
OR(B2<>"Off",B2<>"Null",B2<>"DKNS")),"Yes",
TRUE,"DKNS")

 

Hope that helps

best response confirmed by AL789 (Occasional Contributor)
Solution

@AL789 

I tried to reduce the logic and this is what my formula looks like in D2 of the attached file: 

Nested IF.PNG

Thank you. This worked perfectly for what I am trying to achieve.
Thank you for the response. Unfortunately this didn't provide all of the results I needed, but I found a formula that did.
You’re perfectly welcome!