Forum Discussion
IF using both AND and OR with variable entries
I've looked through quite a few similar posts but haven't found one that matches what I want to do.
In column B, there are 4 choices that can be input - O, M, A, E
In column F, amounts will be manually entered.
In column O, I want to automatically input whether or not preapproval is required based on a preset limit depending on the choice in Column B.
I can get them to work individually -
=IF(AND(B5:B1000="O",F5:F1000<=1999.99),"Not Required","")
=IF(AND(B5:B1000="M",F5:F1000<=7999.99),"Not Required","")
=IF(AND(B5:B1000="A",F5:F1000<=4999.99),"Not Required","")
=IF(AND(B5:B1000="E",F5:F1000<=999.99),"Not Required","")
But need them to all go in the same cell. The following formula returns a "VALUE" error but will hopefully give an idea of what I'm trying to do.
=OR(IF(AND(B5:B1000="O",F5:F1000<=1999.99),"Not Required",""),(IF(AND(B5:B1000="M",F5:F1000<=7999.99),"Not Required","")),(IF(AND(B5:B1000="A",F5:F1000<=4999.99),"Not Required","")),(IF(AND(B5:B1000="E",F5:F1000<=999.99),"Not Required","")))
A sample file is attached. Any help will be greatly appreciated!
Shanna
In O5:
=IF(OR(AND(B5="O",F5<=1999.99),AND(B5="M",F5<=7999.99),AND(B5="A",F5<=4999.99),AND(B5="E",F5<=999.99)),"Not Required","")
Fill down.
5 Replies
As variant
=IF(SUM( ([@[Operating, Maintenance, or Small Tools (O, M or A, E)]]={"O","M","A","E"}) * ([@[Requested Amount]]<={1999.99,7999.99,4999.99,999.99})), "Not required", "")- thank you. The other response worked well for me. I'm curious though, why would SUM be used? I'm not trying to get a total.
In logical operation OR is equivalent of sum and AND is equivalent of multiplication. Background is that 0 is considered as FALSE and any other number as TRUE.
Thus OR(cond1, cond2, cond3) could be used as (cond1+cond2+cond3).
Next, expression like =A1={1,2,3,4} returns something like {FALSE,TRUE,TRUE,FALSE} and if the use it in formula as =(A1={1,2,3,4})*1 result will be {0,1,1,0}. With that SUM((A1={1,2,3,4})*1) returns zero (FALSE) if no one condition met or any other number (TRUE) otherwise.
Above is equivalent of =OR(A1=1, A1=2, A1=3, A1=4)
In O5:
=IF(OR(AND(B5="O",F5<=1999.99),AND(B5="M",F5<=7999.99),AND(B5="A",F5<=4999.99),AND(B5="E",F5<=999.99)),"Not Required","")
Fill down.
- Ooof! I was so close. Thank you! This worked great.
