Excel Logical Test (Table of Contents)
Introduction to Logical Test in Excel
A logical test means the system will check for a given condition, and if that logic is satisfied, then it will display the results according to logic in excel. Logic is anything that we check; suppose if a student secures more than 35 marks, we can consider it as pass otherwise fail. An employee should get a bonus if his salary from 25k to 30k. like this, we can check for any logic in the real world and execute the results accordingly. To perform these results, Excel has multiple logical functions. With the help of those functions, we can perform our logical tests in excel.
Available excel logical functions are IF, AND, OR, NOT, XOR, etc.
How to Use Logical Functions in Excel?
The logical Test in excel is very simple and easy to use. Let’s understand how to use logical functions in excel.
#1 – IF Function
If function checks for the given condition and provides results as per condition satisfaction. Consider a small condition; if age is greater than 50, the result should be Eligible for pension; otherwise, should not be eligible for a pension.
First, understand the IF syntax. Below is the screenshot of IF syntax.
If you observe, first is a logical test which is the condition that should satisfy in excel. The second is Value_If_True means what to print if the condition is true. Last is Value_if_false what to print if the condition is false.
4.8 (8,719 ratings)
View Course
Now input the data and apply the formula in cell B2 as below.
The output will be as given below.
Drag the Formula to the rest of the cells.
We can observe that wherever the age is greater than 60, the results are “Eligible for Pension” if it is less than 60 “Not eligible for pension”.
In addition to IF, there is Nested IF, and we can also write multiple logics as per requirements.
#2 – AND Function
AND logical test is used in excel when we want to check whether two conditions are satisfied or not. We will see the syntax of AND when we give =AND in any cell; we can find the syntax like below.
In the picture, we can observe the syntax as logic 1, logic 2 and so on. How many logics there will be, all should be satisfied; then only the results will be “True”.
Now we will apply the formula to the small logic shown in the picture. We want to know the candidate’s A, B, C who cleared two tests with more than 50 marks. If both tests secured more than 50, then it will display “True” otherwise “False”.
The Formula is applied to cell D2.
The output will be as given below.
Drag the Formula to the rest of the cells.
Observe the above picture B2 is greater than 50 and C2 is greater than 50; hence it is “True”. If we observe candidate B who secured less than 50 in Test 1 and greater than 50 in Test 2, however, as per AND logic, both logics should satisfy then only the result will be “True”. Since candidate A satisfied both logics, hence it is “True”. We can use “AND” logic for better logics application with the combination of “IF”.
#3 – OR Function
OR logical test in excel will give results if anyone logic or condition satisfies. If both the conditions are false, then the results will be false.
Below picture for OR syntax.
If we observe the syntax, it is logic1, logic2, and so on like AND logic, but all the logic should satisfy any logic here. We will consider the same logic of AND, and both the tests should secure more than 50 then only True. In OR logic, if any One test secures greater than 50, then it will be True.
The output will be as given below.
Drag the Formula to the rest of the cells.
Observe the red color box formula and observe the results in column D. All are “True” because all candidates secured greater than 50 in at least in one test.
Change the values of two tests less than 50 and observe for any one candidate.
Now, as the results of both tests are less than 50, hence it is “False”. OR can also be used with the combination of IF for better logic applications.
#4 – XOR Function
XOR logical excel function works in such a way if any One logic satisfies, it will give the result as True if both the logics are satisfied or if both are not satisfied, then it will be False. If we want the results as True, only a single condition should satisfy.
The syntax of XOR is similar to, OR we just need to add X at the beginning.
We will use the same example to apply XOR too.
The output will be as given below.
Drag the Formula to the rest of the cells.
Observe the formula and observe the results. When candidate A satisfies both Test1 and Test2 with more than 50, Falsesimilarly Candidate C does not satisfy both tests, hence False. Candidate B not secured more than 50 in test1 but secured more than 50 in test2, so the condition satisfied hence True.
#5 – NOT Function
The NOT logical test in excel returns False when the condition is satisfied, True when the condition is not satisfied. Let see the syntax of NOT then we will see how we can use it.
We can observe it only has logic that needs to check. Now we will apply to a situation where we can use it. Suppose we want to display false for voting if age is less than 18.
The output will be as given below.
Drag the Formula to the rest of the cells.
Observe the above picture if Age is less than or equal to 18, which means it is giving False if age is greater than 18, it returns True. We can use the NOT function to perform other logical operations with the combination of other excel logical functions like IF, OR, etc.
Things to Remember about Logical Test in Excel
- Logical tests are used to return the values which depend on the satisfaction of the condition in excel. Different logical tests follow different logics in excel.
- If the function returns one value if the condition is satisfied and returns another value if a condition is not satisfied, it returns values that the user provides.
- OR function logic is it will return value as True if any One condition satisfied out of two conditions.
- AND function logic is it will return value as True if both the conditions are satisfied. If any of the condition is not satisfied, it will return a False value.
- XOR excel logical test will return a True value if only one condition satisfies out of two. It will return False if both are satisfied or not satisfied.
- NOT function returns True if a condition is not satisfied and returns False if a condition is satisfied.
- Depend on the situation and requirement; we can use a combination of all these Logical tests in excel.
Recommended Articles
This has been a guide to Logical Tests in Excel. Here we discuss how to use logical functions like IF, AND, OR, XOR, and NOT in Excel, along with practical examples and a downloadable template. You may learn more about excel from the following articles –
