Discussion Re: IF using both AND and OR with variable entries in Excel
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2439101#M102981
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.Fri, 11 Jun 2021 12:02:07 GMTSheilveil2021-06-11T12:02:07ZIF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2436757#M102904
<P>I've looked through quite a few similar posts but haven't found one that matches what I want to do.</P><P>In column B, there are 4 choices that can be input - O, M, A, E</P><P>In column F, amounts will be manually entered.</P><P>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.</P><P> </P><P>I can get them to work individually - </P><P>=IF(AND(B5:B1000="O",F5:F1000<=1999.99),"Not Required","")</P><P>=IF(AND(B5:B1000="M",F5:F1000<=7999.99),"Not Required","")</P><P>=IF(AND(B5:B1000="A",F5:F1000<=4999.99),"Not Required","")</P><P>=IF(AND(B5:B1000="E",F5:F1000<=999.99),"Not Required","")</P><P> </P><P>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.</P><P> </P><P>=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","")))</P><P> </P><P>A sample file is attached. Any help will be greatly appreciated!</P><P> </P><P>Shanna</P>Thu, 10 Jun 2021 19:32:16 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2436757#M102904Sheilveil2021-06-10T19:32:16ZRe: IF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2436810#M102906
<P><LI-USER uid="1076111"></LI-USER> </P>
<P>In O5:</P>
<P> </P>
<P>=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","")</P>
<P> </P>
<P>Fill down.</P>Thu, 10 Jun 2021 19:44:42 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2436810#M102906Hans Vogelaar2021-06-10T19:44:42ZRe: IF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2437007#M102912
<P><LI-USER uid="1076111"></LI-USER> </P>
<P>As variant</P>
<LI-CODE lang="excel">=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", "")</LI-CODE>Thu, 10 Jun 2021 20:55:21 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2437007#M102912Sergei Baklan2021-06-10T20:55:21ZRe: IF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2439079#M102976
Ooof! I was so close. Thank you! This worked great.<BR /><BR />Fri, 11 Jun 2021 11:55:01 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2439079#M102976Sheilveil2021-06-11T11:55:01ZRe: IF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2439101#M102981
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.Fri, 11 Jun 2021 12:02:07 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2439101#M102981Sheilveil2021-06-11T12:02:07ZRe: IF using both AND and OR with variable entries
https://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2440439#M103054
<P><LI-USER uid="1076111"></LI-USER> </P>
<P>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.</P>
<P>Thus OR(cond1, cond2, cond3) could be used as (cond1+cond2+cond3).</P>
<P>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.</P>
<P>Above is equivalent of =OR(A1=1, A1=2, A1=3, A1=4)</P>Fri, 11 Jun 2021 17:28:39 GMThttps://techcommunity.microsoft.com/t5/excel/if-using-both-and-and-or-with-variable-entries/m-p/2440439#M103054Sergei Baklan2021-06-11T17:28:39Z