Updated August 19, 2023
Introduction to Logical Test in Excel
A logical test means the system will check for a given condition, and if that logic is satisfied, it will display the results according to logic in Excel.
Logic is anything we check; suppose if a student secures more than 35 marks, we can consider it a pass; otherwise, fail. An employee should get a bonus if his salary is 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
Suppose the function checks for the given condition and provides results as per condition satisfaction. Consider a small condition; if the age is greater than 50, the result should be Eligible for a pension; otherwise, it should not be eligible for a pension.
First, understand the IF syntax. Below is the screenshot of the IF syntax.
If you observe, the 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.
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 to check whether two conditions are satisfied. We will see the syntax of AND when we give =AND in any cell; we can find the syntax below.
In the picture, we can observe the syntax as logic 1, logic 2, and so on. How much logic there will be, all should be satisfied; 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 candidates A, B, and 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 is a picture of 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 AND logic, and both the tests should secure more than 50, then only True. In OR logic, if any One test secures greater than 50, 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 the results in column D. All are “True” because all candidates secured greater than 50 in at least one test.
Change the values of the two tests to less than 50 and observe for any candidate.
As the results of both tests are less than 50, 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 to be True, only a single condition should satisfy.
The syntax of XOR is similar to OR. We 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 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 test 1 but secured more than 50 in test 2, so the condition was satisfied hence True.
#5 – NOT Function
The NOT logical test in Excel returns False when the condition is satisfied and True when the condition is not satisfied. Let’s see the syntax of NOT and how we can use it.
We can observe it only has logic that needs to check. Now we will apply it to a situation where we can use it. Suppose we want to display false for voting if the 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 the Age is less than or equal to 18, it is giving False if the 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 Tests in Excel
- Logical tests are used to return the values that depend on the condition’s satisfaction in Excel. Different logical tests follow different logic 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 will return the value as True if any One condition is satisfied out of two conditions.
- AND function logic will return the value as True if both conditions are satisfied. If any of the conditions 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.
- We can combine all these Logical tests in Excel depending on the situation and requirement.
Recommended Articles
This has been a guide to Logical Tests in Excel. Here we discuss using 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 –
