Multiple IF, AND, OR in excel won't work

[baz]

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!

[#2]

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))

[#3]

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!!

[#4 · Accepted solution]

@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

[#5]

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!!

[#6]

@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.

[#7]

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

[#8]

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?

[#9]

@baz 

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.

[#10]

Hello again, I still think your orginal formula is just fine. The problem lies with the data. Of course I could be wrong and probably am. So I ran a test. I shortened your original formula up a bit (I hate typing), and your formula worked fine. Here's my test. best to drop it into excel. bob
a b c d e f g h i
27
28 test below== false =IF(AND(E30,E31),"true","false")
29 e29 FALSE =E32<F32
30 e30 FALSE =AND(E32<F32,E32<G32)
31 e31 FALSE AND(E32<F32,E32>G32)
32 orig=== "=IF(E32<F32,1,

IF(OR(AND(E32<F32,E32<G32),

AND(E32<F32,E32>G32)), ""True"", ""False""))" ====>> 3 2 4 = formula answers
33 e29 e30 e31
34
35
36
37

[#11]

@magerr999 

value(a2), value(b2)......  it takes what might be a text and changes it to a numeric value. but what might be easier is to first check your column or row of data using the type command, type(a2), .... it will tell you whether your data is 1 for numeric or 2 for text.

I again looked at your original formula and still don't see anything wrong with it.  But I've been wrong before.  I would go along with the person 's formula that works for you.

odds are, if you retyped the formula from scratch, it will work for you.  imo,  bob

[#12]

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/EWUC5if26MVCqz5XYjL...

[#13]

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.

[#14]

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

[#15]

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

[#16]

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!!