Alok Paul
Microsoft Excel contains lots of functions. Those functions are inbuilt in Excel. In this article, we will discuss the Excel Boolean functions with logical operators. Note that Boolean operators are expressed as functions in Excel.
To explain the Boolean operators with practical use we will use the below data set.
This data set shows the payment of the loan with duration. The payment will be made monthly.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Excel Boolean Functions and Operators
The boolean operator comes from the boolean expression. Boolean expression is commonly used in programming languages. Boolean operators are used to express a boolean expression. The return of the boolean expression is 1 or 0. To understand easily we can express it as True or False respectively.
In Excel, we have 4 boolean operators/functions- NOT, OR, AND, and XOR. Now, we will have an overview of the following boolean functions in Excel.
| Operator | Description |
|---|---|
| NOT | Basic of this function is to return a reverse result. Whatever the argument is, this will return the reverse value of that argument. |
| AND | This compares all the arguments and if all the arguments are satisfying then return TRUE, otherwise FALSE. |
| OR | If any of the arguments satisfy the return will be TRUE. But if all the arguments are dissatisfying then result FALSE. |
| XOR | It is known as “Exclusive OR”. Suppose we are comparing two arguments. If any of the arguments is true, then returns TRUE. But if all the arguments are true or none of the arguments are true then return FALSE. |
Those logical operations are performed when we apply any boolean operation.
| Logical Operator | Meaning | Example | Explanation |
|---|---|---|---|
| = | Equal | =B4=C4 | This formula will compare B4 and C4 if both are equal return TRUE otherwise FALSE. |
| > | Greater than | =B4>C4 | If B4 is greater than C4 the return will be TRUE otherwise FALSE. |
| < | Less than | =B4<C4 | If B4 is less than C4 the return will be TRUE otherwise FALSE. |
| <> | Not Equal | =B4<>C4 | This will compare B4 and C4 and if both are not equal the return TRUE, otherwise FALSE. |
| >= | Greater than or equal | =B4>=C4 | If B4 is greater than or equal to C4 return will be TRUE, otherwise FALSE. |
| <= | Less than or equal | =B4<=C4 | If B4 is smaller than or equal to C4 return will be TRUE, otherwise FALSE. |
4 Examples of Using Boolean Operators in Excel
Now, we will show some examples using boolean functions and operators in Excel.
1. Application of NOT Boolean Operator
The NOT function reverses the result. Converts TRUE into FALSE and vice-versa.
Here we will show the use of the NOT function with 3 examples.
Example 1:
We will apply the NOT function in the data set below.
Step 1:
- Go to Cell F5.
- Write the below code:
=NOT(D5=5)
Step 2:
- Now, press Enter.
Step 3:
- Pull the Fill Handle towards the last cell.
Here, we applied the NOT function with a view to see which data of the cells of the Duration column is equal to 5 years. From the result, we can see that those cells that are equal to 5 are showing FALSE and the rest are showing TRUE.
Example 2:
In this example, we will insert the IF function with the NOT function.
Step 1:
- Write the following formula in Cell F5.
=IF(NOT(D5=5),"Yes","No")
Step 2:
- Then press Enter and see the return.
Step 3:
- Drag the Fill Handle icon towards the last cell.
As the NOT function returns the reverse logical output, we also set a negative result for each cell.
One of the advantages of using the IF function is that we can set the return argument according to our desire.
Read More: How to Use Logical Operators in Excel (11 Examples)
2. Use of Boolean AND Operator in Excel
The AND function checks the arguments with conditions. If all the arguments satisfy conditions, then results TRUE. But if any of the arguments dissatisfy condition then returns FALSE.
Now let’s see 5 examples to be more clear.
Example 1:
Here, we will show a simple example of the AND function. We will check loans that have a duration of more than 5 years. A single condition is applied in this example.
Step 1:
- Go to Cell F5 and put the formula below:
=AND(D5>5)
Step 2:
- Then press Enter to get the return.
Step 3:
- Pull the Fill Handle icon towards the last cell.
Here, we can see how simple to apply the AND operator.
Example 2:
In this example, we will apply multiple conditions in a single formula by applying the AND function each time. We will identify which rows contain a duration more than or equal to 5 years and the total loan is less than $100,000.
Step 1:
- Go to the Cell F5.
- Put the below formula which contains two conditions.
=AND(D5>=5,E5<=100000)
Step 2:
- Now, we can see the return after applying the formula in the below image.
So, we can apply multiple conditions with a single AND function in Excel.
Example 3:
Here, we will apply the nested AND function. Only AND function is used in the formula. Now, see what happens after applying this formula.
Step 1:
- Write the below formula on Cell F5.
=AND(AND(D5>2,D5<10),AND(E5>50000,E5<200000))
Step 2:
- Press Enter and apply for the rest of the cells also.
We planned the formula in the following way. Duration is greater than 2 years and less than 10 years. And the total loan is greater than $50,000 and less than $200,000.
Example 4:
We can insert the If a function with the AND operator. In this way, we can add manipulate the result as per our likings.
Step 1:
- Apply this formula on Cell F5.
=IF(AND(AND(D5>2,D5<10),AND(E5>50000,E5<200000)),"Success", "Failure")
Step 2:
- Run the formula and see what happens.
We can see that the return value is changed. “Success” and “Failure” are set instead of the default.
Example 5:
We can also apply the Cell range without individual cells along with the AND function.
We want to see if all the deposit amount is greater than $1000.
Step 1:
- Apply the formula with range C5:C9 in Cell F5.
=AND(C5:C9>1000)
Step 2:
- Now, get the output after pressing Enter
Here, we simply used a cell range instead of an individual cell number. This also performs smoothly.
Read More: Reference Operator in Excel [Basics + Special Uses]
3. Apply OR Operator in Excel
The OR function checks all the arguments with the condition. If any of the arguments satisfy the condition it returns TRUE. But if all the arguments dissatisfy the return will be FALSE.
Example 1:
In this example, we will find rows whose duration is greater than 5 years or the total loan is greater than $90,000. We applied two conditions in a single formula.
Step 1:
- Go to Cell F5.
- Write the below formula on that cell-
=OR(D5>5,E5>90000)
Step 2:
- Now, press Enter.
Step 3:
- Drag the Fill Handle icon to Cell F9.
In the case of OR function, it provides TRUE as any of the conditions fulfilled.
Example 2:
We will apply cell range instead of an individual cell in this example. We want to know if the deposit money is greater than $2000.
Step 1:
- Insert the below formula to know if any of the deposits is greater than $2000.
=OR(C5:C9>2000)
Step 2:
- Now, press Enter to get the result.
Example 3:
In this example, we will apply a nested function. AND and IF function will also be inserted in the formula. We want to find which objects have a duration greater than 5 years or a total loan greater than or equal to $90,000 and deposit money is greater than $2000.
Step 1:
- Write the following formula on Cell F5.
=IF(OR(OR(D5>5,E5>=90000),AND(C5>2000)),"Yes","No")
Step 2:
- Now, press Enter and get the result.
This is our nested output after applying the boolean operators.
Read More: What is the Order of Operations in Excel (An Ultimate Guide)
4. Function of XOR Operator in Excel
The XOR operator is commonly said: “Exclusive OR”. It justifies in three ways. First, if all the arguments are true, then returns FALSE. Second, if any of the arguments is true returns TRUE. Also, if all the arguments are false returns FALSE.
To explain this operator we introduced a new data set. See the below data set.
This is a coin toss game. First, each player plays 2 rounds. Head means winning of a player, and tail means loos. In the two rounds, if any player wins, i.e. gets head in both rounds, he does not need to play the 3rd round. If any player gets tails in both rounds, he will be disqualified from the game. And if the result is mixed then he will get a chance to play 3rd round. This scenario can be explained easily by the XOR operator.
Step 1:
- After the 2 rounds, the result is updated in the data set.
Now, we will apply the XOR function to identify who will play the 3rd round.
Step 2:
- Apply the formula on Cell F5.
=XOR(C5="Head",D5="Head")
Step 3:
- Then press Enter and drag won the Fill Handle
We get the result. As the result is showing in terms of TRUE and FALSE, it may be suitable all to understand easily.
We will insert the IF function to make it easier for all.
Step 4:
- After inserting the IF function the formula will look like this.
=IF(XOR(C5="Head",D5="Head"),"Yes","No")
Step 5:
- Now, we will get a clear idea from the result below.
We can say now that, 3 players will play the 3rd round and 2 players will not play.
Read More: ‘Not Equal to’ Operator in Excel (With 5 Examples)
Conclusion
In this article, we showed different boolean operators. Added different examples of each of the boolean operators. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.
