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
raceproretiredAsked:
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
2
Bill PrewCommented:
Try:

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

Open in new window


»bp
1

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
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
Bill PrewCommented:
Hmmm,

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


»bp
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes Bill. I realized soon after posting that I cannot rely on Sum formula. :)
0
Rob HensonFinance AnalystCommented:
And 3 + 5 +7 equals 15 anyway!!!
0
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. :)
0
Bill PrewCommented:
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
0
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.
0
raceproretiredAuthor Commented:
That worked nicely Bill
And thanks to all who contributed.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Lol... You are right Rob.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Ian
We all enjoyed the participation. :)
1
raceproretiredAuthor Commented:
The question was not a trick question :)  any one of the correct values in col's  C,D or E would work.
1
raceproretiredAuthor Commented:
Thanks Neeraj, it was a bit of fun :)   but got the job done.
0
Rob HensonFinance AnalystCommented:
Another alternative:

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


»bp
0
raceproretiredAuthor 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
0
raceproretiredAuthor Commented:
I'm up for it Bill,  :)
0
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

0
raceproretiredAuthor 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 :)
1
Bill PrewCommented:
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
1
raceproretiredAuthor Commented:
Anything to save time and works well
Thanks Bill, those beers will go down well :)
1
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.
2
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.