Forum Discussion

Copper Contributor

Aug 12, 2022

Solved

Multiple IF, AND, OR in excel won't work

Hi Please may I get some help. I've been trying to get this IF, AND, OR statement to work and have checked that all statements fit within their respective brackets but I think where I've gone wrong ...

Formulas and Functions

mtarler

Aug 12, 2022

baz

The problem as fixed above is that the 2nd IF statement has a big OR statement as the conditional but no statements for the if true or if false result:

=IF($H$6<F11-15,1,
   IF( OR( IF( AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),0,1), 
           (IF(AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0),0,1))
         ),
         {if true do this},
         {if false do this}
      )
   )

alternatively maybe this would work for you:

=IFS($H$6<F11-15,1,
     OR($H$6<=F11,E12>=0),{true}, 
     A12<=0,{true},
     B12<=0,{true},
     C12<=0,{true},
     D12<=0,{true},
     TRUE, {false}
   )

again I will not assume what value you wanted for the result of that IF() statement being TRUE or FALSE

mtarler

Silver Contributor

Aug 12, 2022

baz

The problem as fixed above is that the 2nd IF statement has a big OR statement as the conditional but no statements for the if true or if false result:

=IF($H$6<F11-15,1,
   IF( OR( IF( AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),0,1), 
           (IF(AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),0,1)), 
           (IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0),0,1))
         ),
         {if true do this},
         {if false do this}
      )
   )

alternatively maybe this would work for you:

=IFS($H$6<F11-15,1,
     OR($H$6<=F11,E12>=0),{true}, 
     A12<=0,{true},
     B12<=0,{true},
     C12<=0,{true},
     D12<=0,{true},
     TRUE, {false}
   )

again I will not assume what value you wanted for the result of that IF() statement being TRUE or FALSE

baz
Copper Contributor
Aug 12, 2022
Hey mtarler
Thanks heaps for your efforts there! I think both of those work, at least when I put them in they give the right response. I think your second reply is the best so far (I just need to get my head around how it works! LOL) because it's so much less complicated. I tried the IFS and couldn't make it work so went with my version which wasn't working.
After reading quite a few articles on Microsoft help I found that this one:

=IF($H$6<=F11-15,1,IF(OR(AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,G12>0))=TRUE,1,0))

works, but it's still really long. I also changed the last less than to a greater than and think it made the difference.
Thanks again for all your help 😄
Baz