Excel Logical Test (Table of Contents)
Logical Test in Excel
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 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?
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. Second is Value_If_True mean what to print if the condition is true. Last is Value_if_false what to print if the condition is false.
4.8 (2,410 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 rest of the cells.
We can observe wherever the age is greater than 60 the results coming as “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 small logic shown in the picture. We want to know the candidates 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 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”. For better logics application we can use “AND” logic 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 rest of the cells.
Observe the formula in the red color box 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 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 rest of the cells.
Observe the formula and observe the results. When candidate A satisfies both Test1 and Test2 with more than 50 it is Falsesimilarly Candidate C not satisfied both tests hence False. Candidate B not secured more than 50 in test1 but secured more than 50 in test2, so 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 is only having logic which 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 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 NOT function with the combination of other excel logical functions like IF, OR etc to perform other logical operations.
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 function returns one value if the condition is satisfied, and it returns another value if a condition is not satisfied then it returns values which are provided by the user.
- 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 condition is not satisfied it will return False value.
- XOR excel logical test will return 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 –
