Forum Discussion
Solved
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 manu...
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.
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)
