Forum Discussion
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 i...
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")))
- 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.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.I pasted your formula in my sheet and received a #NAME? error.
After some investigation, Excel doesn't like those quotes:
Try this one (Uses the double quotes on the key left of the Enter key):
=IF(AND(ISNUMBER(L12),K12="YES"),"POSITIVE",IF(AND(L12="UNDETERMINED",K12="YES"),"NEGATIVE","INVALID"))
