Forum Discussion

SLSmart's avatar
SLSmart
Copper Contributor
Jan 25, 2023

Complex nested IF AND function help

Hello,

I have 4 sets of conditions I am trying to use to determine the overall result. 
the statements are:

if L12 is a number and K12=“YES”, it is “positive” 

 

if L12= “UNDETERMINED” and K12 is “YES”, it is “negative” 

 

if I12=“NTC” and

J12 and K12=“UNDETERMINED”, it is “valid” 

 

if I12=“Ext Ctl”, K12=“YES”, L12=“UNDETERMINED”, it is “valid”

 

I successfully nested the first two statements, however I think I’m using an incorrect function to add the last two. How could I improve my current formula, or is there a simpler way to nest these statements? 

My current formula 

IF(AND(ISNUMBER(L12),K12=“YES”),”POSITIVE”,IF(AND(L12=“UNDETERMINED”,K12=“YES”),”NEGATIVE”,”INVALID”))

excel
Formulas and Functions

4 Replies

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Jan 26, 2023

    Hello SLSmart,

    Try this formula: 

     

     

    =IF(AND(ISNUMBER(L12),K12="YES",I12=""),"POSITIVE",
IF(AND(L12="UNDETERMINED",K12="YES",I12=""),"NEGATIVE",
IF(OR(AND(I12="NTC",L12="UNDETERMINED"),AND(I12="Ext Ctl",L12="UNDETERMINED")),
"VALID","INVALID")))

     

     

     

    • SLSmart's avatar
      SLSmart
      Copper Contributor
      Jan 26, 2023
      Hello, thank you for your response, but that doesn’t seem to solve it.
      I did edit the original post, as I realized I had quite a few typos. Hopefully that helps any unclear parts from the initial post.
      In the first two conditions the “I” column does not need to be addressed, however in the second two conditions I need the “I” column addressed because the result changes if it is an NTC or Ext Ctl.
      • Rodrigo_'s avatar
        Rodrigo_
        Iron Contributor
        Jan 27, 2023

        Hello SLSmart,

        try this: 

        =IF(OR(AND(I12="NTC",J12="UNDETERMINED",K12="UNDETERMINED"),
AND(I12="Ext Ctl",K12="YES",L12="UNDETERMINED")),"VALID",
IF(AND(ISNUMBER(L12),K12="YES"),"POSITIVE",
IF(AND(L12="UNDETERMINED",K12="YES"),"NEGATIVE","INVALID")))

         
        In order to recognized your criteria, the first if statement in IF Function, is your 3 ifs statement.
        If you want a little bit shorter formula, use the IFS Function, and use the correct sequence of your statements.

Resources