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 may be between each logical step of the OR statement?
=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)))))
I'm certainly not a good excel equation creator so if there's a better way of doing it I'd love to learn it 🙂
Thanks!
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- 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.- 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
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
- Hey mtarler
Thanks heaps for your efforts there! I think both of those work, at least when I put them in they give the right response. I think your second reply is the best so far (I just need to get my head around how it works! LOL) because it's so much less complicated. I tried the IFS and couldn't make it work so went with my version which wasn't working.
After reading quite a few articles on Microsoft help I found that this one:
=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))
works, but it's still really long. I also changed the last less than to a greater than and think it made the difference.
Thanks again for all your help 😄
Baz
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))- 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!! - 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!!
