AND OR formula

Hi,
I would like some help in modifying this formula
Logic .......   col B must = BOY     col C = 3 or col D = 5 or col E = 7

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

Many thanks
Ian
Ian BellretiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

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

Open in new window

Regards
Bill PrewIT / Software Engineering ConsultantCommented:
Try:

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

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
Was distracted... Bill's probably right
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I know it has been resolved by Bill, another one is like this... :)
=IF(AND(SUM(C2:E2)=16,B2="BOY"),99, 0)

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Hmmm,

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


»bp
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes Bill. I realized soon after posting that I cannot rely on Sum formula. :)
Rob HensonFinance AnalystCommented:
And 3 + 5 +7 equals 15 anyway!!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In that case the formula should be this...

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

Open in new window

E2 is 11 not 7 as per the formula shown in the description. :)
Bill PrewIT / Software Engineering ConsultantCommented:
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
Rob HensonFinance AnalystCommented:
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.
Ian BellretiredAuthor Commented:
That worked nicely Bill
And thanks to all who contributed.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Lol... You are right Rob.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Ian
We all enjoyed the participation. :)
Ian BellretiredAuthor Commented:
The question was not a trick question :)  any one of the correct values in col's  C,D or E would work.
Ian BellretiredAuthor Commented:
Thanks Neeraj, it was a bit of fun :)   but got the job done.
Rob HensonFinance AnalystCommented:
Another alternative:

=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&D2="Boy11"),99,0)
Bill PrewIT / Software Engineering ConsultantCommented:
Yes, this one was fun, beers at my house later :)


»bp
Ian BellretiredAuthor Commented:
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
Ian BellretiredAuthor Commented:
I'm up for it Bill,  :)
RaminTechnical AdvisorCommented:
Rob was trying to write this:
=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&E2="Boy7"),99,0) 

Open in new window

Ian BellretiredAuthor Commented:
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 :)
Bill PrewIT / Software Engineering ConsultantCommented:
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)

Open in new window


»bp
Ian BellretiredAuthor Commented:
Anything to save time and works well
Thanks Bill, those beers will go down well :)
Rob HensonFinance AnalystCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.