Complex If
in [Functions]
|
Prev: Weeknum formula
Next: Excel 2007 - Personal Macros
From: Rick Rothstein on 5 Apr 2010 12:48 The OR condition, as written, will be met by any value in E2. Think about it... any value in E2 that is not "Not-applicable" will meet the first condition and, since this is an OR, the OR function will evaluate to TRUE. On the other hand, if E2 does equal "Not-applicable", then the second part of the OR condition will be true and, again, since this is an OR, the OR function will evaluate to TRUE. I'm kind of thinking your first expression is what you wanted, just straighten out the parentheses as Dave and Max showed you. -- Rick (MVP - Excel) "PAL" <PAL(a)discussions.microsoft.com> wrote in message news:6809BB2C-B79F-4CE3-955F-8C956B74ACB8(a)microsoft.com... > I had to change it around a bit. > > =IF(AND(U2="YES",OR(E2<>"Not-applicable",E2<>"Red")),"Yes",""). > > It seems to only be recognizing the U2, piece not the OR. Ideas. > > "Max" wrote: > >> Try: =IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red")),"Yes","") >> It was just a parens thingy. High five? hit the YES below >> -- >> Max >> Singapore >> --- >> "PAL" wrote: >> > Any ideas where I went wrong here. Can't leave the cell >> > >> > =IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red"),"Yes",""))
From: Rick Rothstein on 5 Apr 2010 13:03
I didn't explain that exactly right. Let me try again. If E2 is equal to the value "Not-applicable", then it will the second condition true (that is, E2 will not be equal to "Red") and hence, being an OR test, the OR function will evaluate to TRUE. If E2 is equal to any other value, then the first condition will be true and, again since this is an OR test, the OR function will evaluate to TRUE. So, no matter what is in E2, one of the two "not equal" tests will be true and the OR function will evaluate to TRUE... this is no value that you can put into E2 to make both of those test come out FALSE (which is the only way the OR function can return a FALSE value). -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:eOvLW$N1KHA.264(a)TK2MSFTNGP05.phx.gbl... > The OR condition, as written, will be met by any value in E2. Think about > it... any value in E2 that is not "Not-applicable" will meet the first > condition and, since this is an OR, the OR function will evaluate to TRUE. > On the other hand, if E2 does equal "Not-applicable", then the second part > of the OR condition will be true and, again, since this is an OR, the OR > function will evaluate to TRUE. > > I'm kind of thinking your first expression is what you wanted, just > straighten out the parentheses as Dave and Max showed you. > > -- > Rick (MVP - Excel) > > > > "PAL" <PAL(a)discussions.microsoft.com> wrote in message > news:6809BB2C-B79F-4CE3-955F-8C956B74ACB8(a)microsoft.com... >> I had to change it around a bit. >> >> =IF(AND(U2="YES",OR(E2<>"Not-applicable",E2<>"Red")),"Yes",""). >> >> It seems to only be recognizing the U2, piece not the OR. Ideas. >> >> "Max" wrote: >> >>> Try: =IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red")),"Yes","") >>> It was just a parens thingy. High five? hit the YES below >>> -- >>> Max >>> Singapore >>> --- >>> "PAL" wrote: >>> > Any ideas where I went wrong here. Can't leave the cell >>> > >>> > =IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red"),"Yes","")) > |