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
Hello again 🙂
I'm back with my dilemma (link to spreadsheet below, this time), even after being given some most wonderful replies and other options to the formula I'd initially created, with possibly the best coming from mtarler, but I couldn't get it to work with all my spreadsheet requirements. So, I'm asking for help (please help) because I can't get it to work and it's been a few months of trying now. Admittedly, I don't get much time to work on it at work, as I've got field work to get done and I'm halfway through my Land Survey studies with assignments and tests to boot.
I've removed everything from the spreadsheet that's not needed, some 50 tabs of all the equipment to be calibrated, down to 1 tab.
I've created a temporary date, cell H6 (ultimately O1 will be used) that can be modified to test the equations. The next maintenance due date, cell F11, is 26/01/21 (ignore that it's ages ago..haha) and if the date is changed to 12/1/21, then "Maintenance due" pops up in cell H12. 14 days prior to the due date and continues that way until 1 day after the due date. If you type in 27/1/21 then it becomes "Maintenance overdue".
Now here's the area I've been wrestling with to get right. Let's say the maintenance got done on that day, 27/1/21, and so that date is entered in cell A12. I'd like cells B12, C12, D12 & G12 to highlight red and the words in cell H12 to say "FILL IN RED SQUARES", to prevent people from not filling the rest in and simply getting MAINTENANCE DUE to disappear. If the information is placed in those respective cells then cell H12 changes to "DONE".
Ultimately, I need to get Excel, when something becomes due, to notify specific Outlook calendar accounts daily until it's done. The research that I've done into it suggests I have to use another Office 365 app to be the go-between, between Excel and Outlook. Can't remember its name now off the top of my head because that was nearly 3 months ago
Again, thank you for all the help you're able to offer. I'll buy the person a beer (if you come to New Zealand..haha) that solves the puzzle or comes up with a better method.
https://madsenlawrie-my.sharepoint.com/:x:/g/personal/baronc_madsen-lawrie_co_nz/EWUC5if26MVCqz5XYjLO2GEB8vRh282VIr_89fQCMT5RRw?e=gYB6Pp
I'm back with my dilemma (link to spreadsheet below, this time), even after being given some most wonderful replies and other options to the formula I'd initially created, with possibly the best coming from mtarler, but I couldn't get it to work with all my spreadsheet requirements. So, I'm asking for help (please help) because I can't get it to work and it's been a few months of trying now. Admittedly, I don't get much time to work on it at work, as I've got field work to get done and I'm halfway through my Land Survey studies with assignments and tests to boot.
I've removed everything from the spreadsheet that's not needed, some 50 tabs of all the equipment to be calibrated, down to 1 tab.
I've created a temporary date, cell H6 (ultimately O1 will be used) that can be modified to test the equations. The next maintenance due date, cell F11, is 26/01/21 (ignore that it's ages ago..haha) and if the date is changed to 12/1/21, then "Maintenance due" pops up in cell H12. 14 days prior to the due date and continues that way until 1 day after the due date. If you type in 27/1/21 then it becomes "Maintenance overdue".
Now here's the area I've been wrestling with to get right. Let's say the maintenance got done on that day, 27/1/21, and so that date is entered in cell A12. I'd like cells B12, C12, D12 & G12 to highlight red and the words in cell H12 to say "FILL IN RED SQUARES", to prevent people from not filling the rest in and simply getting MAINTENANCE DUE to disappear. If the information is placed in those respective cells then cell H12 changes to "DONE".
Ultimately, I need to get Excel, when something becomes due, to notify specific Outlook calendar accounts daily until it's done. The research that I've done into it suggests I have to use another Office 365 app to be the go-between, between Excel and Outlook. Can't remember its name now off the top of my head because that was nearly 3 months ago
Again, thank you for all the help you're able to offer. I'll buy the person a beer (if you come to New Zealand..haha) that solves the puzzle or comes up with a better method.
https://madsenlawrie-my.sharepoint.com/:x:/g/personal/baronc_madsen-lawrie_co_nz/EWUC5if26MVCqz5XYjLO2GEB8vRh282VIr_89fQCMT5RRw?e=gYB6Pp
so it appears you have added some helper columns (R:X) to identify the different 'states' which is great. sometimes people try hard to avoid those "extra" columns but they can be REALLY helpful to easily see what is going on and for users of the sheet to see also.. That said, it appears you have worked out how to make the formula work (if not let me know). A couple very minor items I might mention is that doing =IF( [condition] , 1, 0) is not needed as the output of the [condition] is already binary True/False and if you want or need a 1/0 instead of True/False just add a --( [condition] ). Another minor point is that instead of IF( ... IF( ... IF ( ... you can use IFS() to get rid of all that nesting. I would avoid IFS if you have big complicated calculations you want to avoid getting calculated but in your case, it is ideal for using IFS().
Finally, the 'tool' I think you want to look into is Power Automate and create a 'Flow' that can check you worksheet periodically and take action, like email, based on a certain condition. I'm no expert on that but they have some good example you can check out. Just go to the Power Automate page and type excel in the search bar. hope that helps.
Finally, the 'tool' I think you want to look into is Power Automate and create a 'Flow' that can check you worksheet periodically and take action, like email, based on a certain condition. I'm no expert on that but they have some good example you can check out. Just go to the Power Automate page and type excel in the search bar. hope that helps.
- 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- 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 🙂- 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!!