AND _ OR functions in Excel

How to use AND operation in Excel

AND function is a conditional function which returns only two values, either TRUE or FALSE. AND returns true when all of the arguments written inside the AND function are True.

The syntax for the Excel AND function is as follows:

=AND(logical1, [logical2], …)

Where logical is the condition you want to check to determine whether the given argument is true or false.

Let us implement the AND function in excel.

Suppose we have the following Data Set:

Now we want to add an extra field named STATUS which will display TRUE in case of a student passes both the theory and practical exams.

(Our passing criteria is 40 marks for both theory and practical)

If any of the exams are not passed then the status will return FALSE.

To implement this, we are using the below formula and copying it to relevant cells.

We, get our output like this:

So, we have tested two conditions and if both the conditions met the requirement only then the function is going to print TRUE.

But, for a user who has not designed this sheet and is asked to analyse the sheet, he/she will not be able to understand what this TRUE and FALSE corresponds to.

In that case we can use the AND function inside an IF statement to produce desirable customized results.

To use the AND function inside an IF function follow the below formula and customise according to your need.

=IF(AND(C2>40, B2>40), “PASS”, “FAIL”)

Now instead of returning TRUE and FALSE as the result, it will return PASS and FAIL as we have specified inside the IF statement. Without the AND function, it would have required two separate IF function nested inside one. 

You can add as many arguments inside (max is 255) the AND function, but it should have at least one argument, the rest are optional. If the cell referenced inside the AND function is empty it will throw an #VALUE! Error.

And the output for the above formula is given below:

Observe the output, our function is working quite well. The Student with id 2 has passed in Practical but failed in Theory, hence his result stands FAIL. The student with ID 4 has passed in theory but failed in practical, here also the status remains FAIL. Therefore, we have successfully managed to pass all the test cases here.

Using OR function in Excel

OR is also a conditional function, but it differs from AND in the way that if any of the argument stands TRUE, the end result OR will provide is TRUE. The validity of other arguments will not affect the result of OR function if any of the argument is determined to be TRUE.

The syntax for the Excel’s OR function is as follows:

=OR(logical1, [logical2], …)

Let us understand OR function with the help of an example.

Suppose we have the following data:

And now we want to add an extra field to the right to determine whether the transaction was for banana or mango. In both the cases we want our formula to return true.

To implement this let’s try out the below formula:

=OR(A2=”Banana”,A2=”Mango”)

and copy the formula to the relevant cells.

We get the output as given in below screenshot:

See, only those transactions are labelled TRUE in which Banana or Mango is there in the transaction.

• You can also give AND functions as an argument to OR functions and vice versa.
• You can also pass OR functions as an argument to IF statements as we had done above with the student-marks data.

Let us seen an example combining both the above options.

We are trying this formula

=IF(OR(B2>90,C2>90),”Pass with DISTINCTION”,IF(OR(B2<40,C2<40),”Fail”,”Pass”))

We are supposed to get PASS or FAIL as the status as we have mentioned above. But we have added an extra functionality to determine whether the student has passed with flying colours.

If any of the marks of the student is greater than 90 and he has passed in both the categories, then we are going to give that student a PASS WITH DISTINCTION status.

Let’s implement this and see our result.

Hurrah! we have got our result now with three categories of status.

In this way you can also implement complex conditional statements to your worksheet and make your work pleasing.

If you have read till here, you have now gained a lot.

Keep learning! Stay focused. Peace.