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

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 🙂
Your method works, thank you. I see you removed the extra IF statement at the beginning. I'll need to look deeper into it now and have a think. Thanks again!!
- magerr999
  Copper Contributor
  Aug 12, 2022
  baz
  sounds like you found the fix. i have always had trouble with the AND, OR . One hint is to make sure the data you are searching against are numbers. if not you may need to use the value() function.; that will change any alpha into a numeric value.
  - baz
    Copper Contributor
    Aug 12, 2022
    Gidday magerr999
    Thanks for that info. Yeah, I discovered I was using the OR function incorrectly. Here's the update I made to it that works:
    =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))
    
    but it's blooming long. I think mtarler has come up with the best and shortest version I just need to test it properly and try understand how it works! LMAO
    
    Haven't tried the value() function yet. Do you place the alpha values inside the brackets? And is it a case of A=1, B=2 etc?
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!!