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

Martin_Weiss

Bronze Contributor

Aug 12, 2022

Hi baz

this is really a tricky one, but maybe this could work:

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

baz

Copper Contributor

Aug 12, 2022

Hey Martin,
Thanks so much for your help. For a while there I was getting despondent, thinking either my question was dumb or too hard. I will certainly give your version a go and let you know how I get on. Thanks again!!

Forum Discussion

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

Resources