Forum Discussion
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 ...
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
Gidday Matt
Thank you for getting back to me. Your previous IFS equation you created for me was brilliant. Unfortunately, I was struggling to understand it in the timeframes I've had to work on the file. I ended up doing what I know to try and move forward. But, I'd really like to learn and use the IFS function.
If you have a free moment and are able to help with the shortened versions (or just one of them) of my equations that would be great! I can then try and learn how it works in situ.
Thanks again for all your help
Baz
Thank you for getting back to me. Your previous IFS equation you created for me was brilliant. Unfortunately, I was struggling to understand it in the timeframes I've had to work on the file. I ended up doing what I know to try and move forward. But, I'd really like to learn and use the IFS function.
If you have a free moment and are able to help with the shortened versions (or just one of them) of my equations that would be great! I can then try and learn how it works in situ.
Thanks again for all your help
Baz
your welcome. Looking back I actually don't know if that IFS() was even right but then again I think there was something wrong with the original equation. Basically that original equation has a major section looking at an OR of many AND statements and each AND statement returns a 1 for a FALSE value. So if ANY part of ANY of the AND statements is false that particular AND statement returns a 1 and then the OR returns a TRUE. But you have multiple cases of cell X <0 and that same cell X > 0 in those comparisons and since at least 1 of those statements is FALSE then at least 1 AND is FALSE returning at least one 1 meaning that OR is ALWAYS TRUE.
My IFS() statement was trying to go through the possibilities step by step (if xyz is true then I know the output is ..., if not but I know zzz is true then the output is ... and so on). I went down the line checking each condition (or more correctly checking for the opposite of the condition since the false output returned a 1). At the end I should have in theory checked back with the first condition as that was the only one left but I got confused by the original broken logic since all cases lead to true.
I hope all this discussion is helpful and not just more confusing.
If the question was more about how the IFS() statement works in general it is simply a cascading or sequential set of IF() statements so IF A then a, but if not then IF B then b, but if still not then IF C then c, and so on. The advantage is that it is a single function so you don't have tons of ( ) to line up.
And remember you can use ALT-ENTER to create a new line and help organize those complex formulas into something more readable 🙂
My IFS() statement was trying to go through the possibilities step by step (if xyz is true then I know the output is ..., if not but I know zzz is true then the output is ... and so on). I went down the line checking each condition (or more correctly checking for the opposite of the condition since the false output returned a 1). At the end I should have in theory checked back with the first condition as that was the only one left but I got confused by the original broken logic since all cases lead to true.
I hope all this discussion is helpful and not just more confusing.
If the question was more about how the IFS() statement works in general it is simply a cascading or sequential set of IF() statements so IF A then a, but if not then IF B then b, but if still not then IF C then c, and so on. The advantage is that it is a single function so you don't have tons of ( ) to line up.
And remember you can use ALT-ENTER to create a new line and help organize those complex formulas into something more readable 🙂
- Hi Matt,
Thank you for all that info and for explaining where I've gone wrong. You are right! Too many IF's, AND's and OR's so I've basically restarted all the equations and learnt how to use the IFS option. Got a lot to complete but thank you for encouraging me to go down that route instead. It was also far easier than I was making it out to be (the IFS option that is) HAHA
Also, thanks a tonne for the ALT-ENTER option. Sooo cool!!