Forum Discussion
Multiple IF, AND, OR in excel won't work
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
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))
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!!
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.
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?i keep looking at your original formula and I still can't see anything wrong with it, I really don't. I think it's fine. It might have something to do with your data. Or believe it or not, sometimes the formula just won't work and there is nothing wrong with it. I found that if I retyped it in a different cell it will then work fine. good luck.
