Jun 10 2021 12:32 PM
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
Jun 10 2021 12:44 PM
SolutionIn 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.
Jun 10 2021 01:55 PM
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", "")
Jun 11 2021 04:55 AM
Jun 11 2021 05:02 AM
Jun 11 2021 10:28 AM
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)
Jun 10 2021 12:44 PM
SolutionIn 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.