SOLVED
Home

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

Highlighted
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

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