AND OR formula

HI,

pls try

=IF(AND(OR(C2=3,D2=5,E2=7),AND(B2="BOY"),99),0)

Select allOpen in new window

Regards

Was distracted... Bill's probably right

I know it has been resolved by Bill, another one is like this... :)

=IF(AND(SUM(C2:E2)=16,B2="BOY"),99, 0)

Select allOpen in new window

Hmmm,

The problem with a SUM approach is that 1,1,14 also sum to 16, but wouldn't meet the criteria, right?

---

»bp

Yes Bill. I realized soon after posting that I cannot rely on Sum formula. :)

And 3 + 5 +7 equals 15 anyway!!!

In that case the formula should be this...

=IF(AND(OR(C2=3,D2=5,E2=11 ),B2="BOY"),99, 0)

Select allOpen in new window

E2 is 11 not 7 as per the formula shown in the description. :)

Well, depends on if the sample formula, or "logic" text was right.  Maybe it's a trick question :)

Naturally my response in #a42396889 above can be adjusted to which ever is right, hopefully it showed the right way to AND() and OR() the needed conditions...

---

»bp

Still wouldn't sum to 16, would be 19. ;-)

And its an OR for those 3 cells anyway, only one needs to match for the OR to return TRUE.

That worked nicely Bill
And thanks to all who contributed.

Lol... You are right Rob.

@ Ian
We all enjoyed the participation. :)

The question was not a trick question :)  any one of the correct values in col's  C,D or E would work.

Thanks Neeraj, it was a bit of fun :)   but got the job done.

Another alternative:

=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&D2="Boy11"),99,0)

Yes, this one was fun, beers at my house later :)

---

»bp

Rob I think you may have misunderstood the sample I gave.
The 11 in COL E.. I threw in was a red herring. The rules were fulfilled when
Col C had a 3 and for good measure Col D had a 5 value.
as per following
Logic .......   col B must = BOY     col C = 3 or col D = 5 or col E = 7

Hope that clears things

I'm up for it Bill,  :)

Rob was trying to write this:

=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&E2="Boy7"),99,0) 

Select allOpen in new window

Yes that also worked...... well done Ramin for spotting it and to Rob for his effort
which proves there's more than one way to skin a banana :)

And for what it's worth, I'd probably reverse the AND() conditions, like below.  Let it check the simple = condition before the more complex OR condition.  There is a tiny chance that could be a wee bit faster, if Excel is smart enough to stop evaluating the multiple pieces of the AND as soon as it hits the first false one.  So maybe when BOY is not matched, it doesn't waste time doing the three OR comparisons...

=IF(AND(B2="BOY",OR(C2=3,D2=5,E2=7)),99,0)

Select allOpen in new window

---

»bp

Anything to save time and works well
Thanks Bill, those beers will go down well :)

Thank you Ramin. Yes, it was a typo.

Sadly as one of the few UK representatives on EE, it is too far to come for Bill's beer.