Combining OR and And in a formula

[richmark]

Hello,

I want the formula that will calculate the following:
The value in Cell B3 or Cell B4 must be equal to 1, and Cell B5 must be equal to or greater than 3, in order for the answer in Cell B6 to be "Yes"

Can someone help? Thank you

[dilipandey]

HI richmark,

welcome to the forum.

Suggest you to change the thread title to reflect the query you are asking, see forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey
<click on below * if this helps>

[امين اسماعيلي]

=IF(AND(B3=1,B4=1,B5>=3),"Yes","")
if the one of your condition is not true , B6 is empty you can change "" to everything that you want

[Fotis1991]

Also امين اسماعيلي, welcome to the forum.

Pls take some minutes to read forum rules, because rule 7 of the forum says:

.

.7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

Do you believe that dilipandey couldn't suggest a solution?

[richmark]

Hi DILIPandey,

Thank you for letting me know. I changed it but I wasn't sure how to make my title even more specific because I don't know the proper terminology. I hope my new title is satisfactory. Richmark

[Tony Valko]

Does this do what you want:

=IF(AND(OR(B3=1,B4=1),N(B5)>=3),"Yes","")

[richmark]

Hello. Thank you to the person from Iran for your response. However, it's not giving me the correct response. It returns "Yes" only if all 3 conditions are met. That is, the way you have written it, B3 and B4 both must be 1 and B5 must be >= to 3 to return a "yes". I only need B3 OR B4 to be 1 (not both), and B5 >=3 to meet the condition and return a "Yes". "No" is the other return in the empty quotes. Thank you

[richmark]

Yes Biff, that does it. I appreciate it. I do have two questions and hope you don't mind.

1. What is the "N" before (B5) mean? I have not seen that.

2. Does it read like this:
If B3 and or B4 =1, and B5 is => 3, then the answer is "Yes"

Thank you very much. Richmark

[Tony Valko]

The N(...) function returns the numeric value of its reference. If its reference is a number then N(...) returns that number. If the reference is a TEXT value then N(...) returns 0.

I used it just to make the formula robust against TEXT being entered in cell B5. If B5 contains a text value then:

B5>=3 = TRUE

This could lead to an incorrect result. In Excel, a text entry has greater value than ANY number.

="X">100000000000000 = TRUE
=100000000000000<"X" = TRUE

So, if B5 contains a text entry (say it happened by mistake) then:

N(B5) = 0 then 0>=3 = FALSE then AND(...) = FALSE then IF(...) returns blank "".

[Tony Valko]

Pls take some minutes to read forum rules, because rule 7 of the forum says:

.7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

What's the definition of a "senior member" ? Where is that defined in the rules?

[Fotis1991]

If you think that a member(like Dilip, in this case) having 7000 posts and maximum scale of reb(11 scales), is not the definition of a SENIOR MEMBER of this forum then you can keep your opinion for you. I, just don't agree with your opinion and let me to believe that this is the definition.

[FDibbins]

Tony, you make a good point.

But I think that while not specifically stated in the rules, the implication is that a senior member is some-one who has been active here for a while (long membership/lots and lots of posts)

[richmark]

Thanks Tony (or is it Biff?). That makes sense. FWIW, you helped me, and senior member or not, that's what matters. I appreciate your time and expertise.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Fotis1991]

@ richmark

..Tony (or is it Biff?)

Wanting to be clear, i have to say that Tony is a Microsoft MVP. So his Excel knowledge is indisputable.

Whether arguing about the rules or anything else, it is something completely different ..

[Tony Valko]

Tony, you make a good point.

But I think that while not specifically stated in the rules, the implication is that a senior member is some-one who has been active here for a while (long membership/lots and lots of posts)

Would I be considered a senior member?

[Tony Valko]

You're welcome. Thanks for the feedback!

[FDibbins]

Would I be considered a senior member?

Seeing as you live in Pitts, I would say yes.

Just kidding, but yes I would say so, based on your time with the forum and the qty posts/activity you have

[Tony Valko]

Cool!

I'm just getting started!

[Tony Valko]

you can keep your opinion for you. I, just don't agree with your opinion

What opinion are you referring to?

I haven't expressed an opinion on this matter. I simply asked a question.

[Fotis1991]

What opinion are you referring to?

I haven't expressed an opinion on this matter. I simply asked a question.

As looks that my Poor English didn't helped(for 1 more time) me to understand that this was only a question, i have to apologize for my reply! So pls keep only this part of it.

..a member(like Dilip, in this case) having 7000 posts and maximum scale of reb(11 scales), is the definition of a SENIOR MEMBER of this forum

Also.

..Cool!

I'm just getting started!

I hope to do this. Not like the first time(One year ago...)