How to use AND & OR Functions in Microsoft Excel?

Brief Introduction

           Microsoft Excel provides logical functions (like AND & OR Functions) to work with the logical values. These functions are AND & OR. We can use these functions when we want to carry out more than one comparison in our formula or test multiple conditions instead of just one. As well as logical operators, Microsoft Excel logical functions return either TRUE or FALSE when their arguments are evaluated.

           

          The Microsoft Excel AND function is the most popular member of the logical functions family. It comes in handy when we have to test multiple conditions and make sure that all of them are met. Technically, the AND function tests the conditions we specify and returns TRUE if all of the conditions evaluate to TRUE, otherwise it returns to FALSE.

            

              As well as AND, the Microsoft Excel OR function is another basic logical function that is used to compare two values or statements. The difference is that the OR function returns TRUE if at least one of the arguments evaluates to TRUE and returns FALSE if all arguments are FALSE.

Purpose:

To Compare or Test the values & return either TRUE or FALSE when their arguments are evaluated.

 Syntax of AND & OR Functions:

The syntax of AND & OR Functions in Excel are as follows;

1. =AND(logical1, [logical2],…)
2. =OR(logical1, [logical2],…)

Return value:

TRUE or False Values.

Arguments:

• Range - The range of cells on which we want to apply the criteria against.
• Criteria - The criteria (Condition) used to determine which cells to compare or Test.

Logical operators

          When you are using a test with Microsoft Excel AND & OR Functions, we can use any of the following logical operators;

Comparison operator	Meaning	Example
=	equal to	A1=D1
>	greater than	A1>D1
>=	greater & or equal to	A1>=D1
<	less than	A1<d1
<=	less than & or equal to	A1<=D1
<>	not equal to	A1<>D1

You can download free the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below download icon,

  

          

              This article explains in simple terms how to use AND & OR Functions. It takes a step-by-step approach, & following below contents we will touch in this post.

1. Microsoft Excel AND Function explanation through various examples.
2. Microsoft Excel OR Function explanation through various examples.

Let’s start to learn something new about the AND & OR Functions of Microsoft Excel.                

1.  Microsoft Excel AND Function - Example 1.

             Microsoft Excel AND Function is used to test or compare all of given arguments evaluate to True or false, read the below note,

Note          When we are Using Microsoft Excel AND Function, keep in mind that Returns will be TRUE if all of the arguments evaluate to TRUE, otherwise FALSE.

            

So now we explain AND Function through various examples, as shown below,

Explanation: - In this example we have taken the information about a business which deal with furniture, from "Row 2" to "Row 8" which contains the information of Items, It’s Cost, It’s Sale & the last "E" column contains a formula that how we can use the AND Function to get our target. As we see that the cell "E5" contains the AND Function & "B5, C5 & D5” are our given information, so we have applied two arguments in this example, our 1^st argument is "B5=B5" & second argument is "C5<D5", in selected cell "E5" the AND Function evaluate both arguments therefore return is "TRUE".              

In this scenario, we have used a formula like below,

=AND(B5=B5,C5<D5)

Example 2.

               If in the case when we use any word or text in an argument, so then see the below example,

Explanation: - In this example we see that from "Row 2" to "Row 8" which contains the information about Items, It’s Cost, It’s Sale & the last "E" column contains the formula that how we can use the AND Function to get our target. As we see that the cell "E5" contains the AND Function & "B5, C5 & D5” are our given information, so we have applied two arguments in this example, our 1^st argument is "B5="Sofa" & second argument is "C5<D5", in selected cell "E5" the AND Function evaluate both arguments so therefore return is "TRUE".  

In this scenario, we have used a formula like below,

D(B5="Sofa",C5<D5)

Example 3.

             If in the case when we use more than two arguments, so then see the below example,

Explanation: - In this example we see that from "Row 2" to "Row 8" which contains the information of Items, It’s Cost, It’s Sale & the last "E" column contains the formula that how we can use the AND Function to get our target. As we see that the cell "E5" contains the AND Function & "B5, C5 & D5” are our given information, so we have applied three arguments in this example, our 1^st argument is "B5="Curtains", second argument is "C5<D5" & our third argument is "B5>=D5", in selected cell "E5" the AND Function doesn’t evaluate three arguments therefore return is "FALSE".

In this scenario, we have used a formula like below,

=AND(B5="Curtains",C5<D5,B5>=D5)

Example 4.

                 If in the case when we want to change or replace return TRUE & FALSE with other words like Yes & No, so then see the below example,

Explanation: - In this example we see that from "Row 2" to "Row 8" which contains the information about Items, It’s Cost, It’s Sale & the last "E" column contains a formula that how we can use the AND Function to get our target. As we see that the cell "E6" contains the AND Function & "B6, C6 & D6” are our given information, so we have applied two arguments in this example, our 1^st argument is "B6="Air-Conditioner" & second argument is "C6<D6", & also mention in the formula that "Yes & NO" will be replaced by "TRUE & FALSE", so in selected cell "E6" the AND Function evaluate both arguments therefore the return is "Yes".

In this scenario, we have used a formula like below,

=IF(AND(B6="Air-Conditioner",C6<D6),"Yes","No")

2. Microsoft Excel OR Function - Example 1.

                Microsoft Excel OR Function is used to test or compare any of given arguments evaluate to True or false, read the below note,

Note          When we are Using Microsoft Excel OR Function, keep in mind that Returns will be TRUE if any of the argument evaluate to TRUE, otherwise FALSE.

 So now we explain OR Function through various examples, as shown below,

Explanation: - In this example we see that from "Row 2" to "Row 8" which contains the information about Items, It’s Cost, It’s Sale & the last "E" column contains the formula that how we can use the OR Function to get our target. As we see that the cell "E5" contains the OR Function & "B5, C5 & D5” are our given information, so we have applied two arguments in this example, our 1^st argument is "B5="Bed" & our second argument is "B5="Sofa", in selected cell "E5" the OR Function evaluate our second argument therefore return is "TRUE".

In this scenario, we have used a formula like below,

=OR(B5="Bed",B5="Sofa")

Example 2.

             If in the case when we use empty cell among the given arguments, so then see the below example,

Explanation: - In this example we see that from "Row 2" to "Row 8" which contains the information about Items, It’s Cost, It’s Sale & the last "E" column contains the formula that how we can use the OR Function to get our target. As we see that the cell "E5" contains the OR Function & "B5, C5 & D5” are our given information, so we have applied two arguments in this example, our 1^st argument is "C5="" & our second argument is "D5="", in selected cell "E5" the OR Function evaluate our second argument therefore return is "TRUE". ("" Means empty cell.)

In this scenario, we have used a formula like below,

=OR(C5="",D5="")