SOLVED

IF using both AND and OR with variable entries

%3CLINGO-SUB%20id%3D%22lingo-sub-2436757%22%20slang%3D%22en-US%22%3EIF%20using%20both%20AND%20and%20OR%20with%20variable%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2436757%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20looked%20through%20quite%20a%20few%20similar%20posts%20but%20haven't%20found%20one%20that%20matches%20what%20I%20want%20to%20do.%3C%2FP%3E%3CP%3EIn%20column%20B%2C%20there%20are%204%20choices%20that%20can%20be%20input%20-%20O%2C%20M%2C%20A%2C%20E%3C%2FP%3E%3CP%3EIn%20column%20F%2C%20amounts%20will%20be%20manually%20entered.%3C%2FP%3E%3CP%3EIn%20column%20O%2C%20I%20want%20to%20automatically%20input%20whether%20or%20not%20preapproval%20is%20required%20based%20on%20a%20preset%20limit%20depending%20on%20the%20choice%20in%20Column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20get%20them%20to%20work%20individually%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B5%3AB1000%3D%22O%22%2CF5%3AF1000%26lt%3B%3D1999.99)%2C%22Not%20Required%22%2C%22%22)%3C%2FP%3E%3CP%3E%3DIF(AND(B5%3AB1000%3D%22M%22%2CF5%3AF1000%26lt%3B%3D7999.99)%2C%22Not%20Required%22%2C%22%22)%3C%2FP%3E%3CP%3E%3DIF(AND(B5%3AB1000%3D%22A%22%2CF5%3AF1000%26lt%3B%3D4999.99)%2C%22Not%20Required%22%2C%22%22)%3C%2FP%3E%3CP%3E%3DIF(AND(B5%3AB1000%3D%22E%22%2CF5%3AF1000%26lt%3B%3D999.99)%2C%22Not%20Required%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20need%20them%20to%20all%20go%20in%20the%20same%20cell.%20The%20following%20formula%20returns%20a%20%22VALUE%22%20error%20but%20will%20hopefully%20give%20an%20idea%20of%20what%20I'm%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOR(IF(AND(B5%3AB1000%3D%22O%22%2CF5%3AF1000%26lt%3B%3D1999.99)%2C%22Not%20Required%22%2C%22%22)%2C(IF(AND(B5%3AB1000%3D%22M%22%2CF5%3AF1000%26lt%3B%3D7999.99)%2C%22Not%20Required%22%2C%22%22))%2C(IF(AND(B5%3AB1000%3D%22A%22%2CF5%3AF1000%26lt%3B%3D4999.99)%2C%22Not%20Required%22%2C%22%22))%2C(IF(AND(B5%3AB1000%3D%22E%22%2CF5%3AF1000%26lt%3B%3D999.99)%2C%22Not%20Required%22%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20attached.%20Any%20help%20will%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShanna%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2436757%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2436810%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20using%20both%20AND%20and%20OR%20with%20variable%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2436810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076111%22%20target%3D%22_blank%22%3E%40Sheilveil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20O5%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(AND(B5%3D%22O%22%2CF5%26lt%3B%3D1999.99)%2CAND(B5%3D%22M%22%2CF5%26lt%3B%3D7999.99)%2CAND(B5%3D%22A%22%2CF5%26lt%3B%3D4999.99)%2CAND(B5%3D%22E%22%2CF5%26lt%3B%3D999.99))%2C%22Not%20Required%22%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2437007%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20using%20both%20AND%20and%20OR%20with%20variable%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2437007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076111%22%20target%3D%22_blank%22%3E%40Sheilveil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUM(%0A%20(%5B%40%5BOperating%2C%20Maintenance%2C%20or%20Small%20Tools%20(O%2C%20M%20or%20A%2C%20E)%5D%5D%3D%7B%22O%22%2C%22M%22%2C%22A%22%2C%22E%22%7D)%20*%0A%20(%5B%40%5BRequested%20Amount%5D%5D%26lt%3B%3D%7B1999.99%2C7999.99%2C4999.99%2C999.99%7D))%2C%0A%20%22Not%20required%22%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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 (New 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)