SOLVED

IF using both AND and OR with variable entries

Copper Contributor

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

5 Replies
best response confirmed by Sheilveil (Copper Contributor)
Solution

@Sheilveil 

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.

@Sheilveil 

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", "")
Ooof! I was so close. Thank you! This worked great.

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.

@Sheilveil 

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)

1 best response

Accepted Solutions
best response confirmed by Sheilveil (Copper Contributor)
Solution

@Sheilveil 

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.

View solution in original post