Excel "And" and "Or" Command

I have taken notice that the "And" and "Or" command is not consistently give the correct answer.

1) "And" formula =if(And(B2,C2>3), "Yes", "No")

 input Cell B2=4 and Cell C2=3, yield answer "No"

Flip the input Cell B2=3, and Cell C2=4, yield answer "Yes"

2) "Or" Formula =if(Or(B2,C2>3), "yes","No")

 Input Cell B2=0 and Cell C2=3, yield "No"

Flip the input Cell B2=3, Cell B2=0, yield "Yes"

Hope someone can enlighten on this.

St-Lim

Posted 2016-01-19T09:14:54.880

Reputation: 41

Answers

You're not using it correctly.

=if(And(B2,C2>3), "Yes", "No")

should be

=if(And(B2>3,C2>3), "Yes", "No")

You need a condition for each argument of AND, not a list of cells to test against a single condition.

Same for OR

=if(Or(B2>3,C2>3), "yes","No")

This allows you to test different conditions per argument, which is the designed behavior.

Currently it will be looking at your first argument, and decide if it thinks it's true or false based purely on what it finds in that cell, which will have odd behavior depending on what excel thinks the appropriate response would be (e.g. 0 = false, 1 = true).

Jonno

Posted 2016-01-19T09:14:54.880

Reputation: 18 756

2Jonno, The AND() function does NOT need a condition to test for True or False. Any non-zero numeric value will evaluate to True. A Zero will evaluate to false. To test this try this formula and enter values into B2: =IF(B2,"True","False") – B540Glenn – 2016-01-19T15:28:35.697

@B540Glenn You seem to have ignored my last sentence. – Jonno – 2016-01-19T15:29:34.893

1Not ignored, but I didn't find it clear about not specifically requiring a conditional statement in the AND() finction. – B540Glenn – 2016-01-19T15:53:47.027

St-Lim,

Your formulas are working as they should.

AND: =if(And(B2,C2>3), "Yes", "No")

With input Cell B2=4 and Cell C2=3, the answer "No" is correct. B2 evaluates to True. C2 is not greater than 3 so that evaluates to False. True and False = False.

With input Cell B2=1 and Cell C2=5, the answer "Yes" is correct. B2 evaluates to True. C2 is greater than 3 so that evaluates to True.
True and True = True

OR: =if(Or(B2,C2>3), "yes","No")

With input Cell B2=3, and Cell C2=4, the answer should be "Yes" Any non-zero numeric values in B2 will evaluate to True. 4 is greater than 3 so C2>3 also evaluates to True.
True or True = True.

With input Cell B2=0, and Cell C2=4, the answer should also be "Yes"
B2 = 0 is False, 4 is greater than 3. False or True = True.

With input Cell B2=0, and Cell C2=1, the answer should be "No"
B2 = 0 is False, 1 is NOT greater than 3. False or False = False.

B540Glenn

Posted 2016-01-19T09:14:54.880

Reputation: 1 045