Logical test means having a logical output which is either true or false, in excel we can perform logical test to any of the situations, the most commonly used logical test is by using the equals to operator which is “=” is we use =A1=B1 in cell A2 then it will return true if the values are equal and false if the values are not equal.
Excel Logical Test (Table of Contents)
What is Logical Test in Excel?
In excel at the beginning stages of learning it is quite a difficult task to understand the concept of logical tests. But once you master this, it will be a valuable skill to your CV. More of often than not in excel we use a logical test to match multiple criteria’s and arrive at the desired solution.
In excel we have as many as 9 logical formulas. Go to the Formula tab and click on the Logical Function group to see all the logical formulas.
Some of them are frequently used formulas and some of them are rarely used formulas. In this article, we will cover some of the important Excel logical formulas in real-time examples. All the Excel logical formulas work on the basis of TRUE or FALSE of the logical test we do.
How to Use Logical Function in Excel?
Below are the examples of logical Function in excel.
#1 – AND & OR Logical Function in Excel
Excel AND & OR functions works completely opposite to each other. AND condition in excel requires all the logical tests to be TRUE and on the other hand OR function requires any of the logical tests to be TRUE.
For example, look at the below examples.
We have a student name, Marks 1, and Marks 2. If the student scored more than 35 in both the exams then result should be TRUE, if not result should be FALSE. Since we need to satisfy both the conditions we need to use AND logical test here.
Example #1- AND Logical Function in Excel
Step 1: Open AND function first.
Step 2: First logical 1 is Marks 1 is >35 or not. So test the condition.
Step 3: Second test is Marks 2 is >35 or not. So apply the logical test.
Step 4: We have only two conditions to test. So we have applied both the logical tests. Now close the bracket.
If both the conditions are satisfied then formula by default returns TRUE or else returns FALSE as the result.
Drag the Formula to rest of cells.
In cell D3 & D4 we have got FALSE as the result because in Marks 1 both the students scored less than 35.
Example #2 – OR Logical Function in Excel
OR is completely different from AND function. OR in Excel requires only one condition to be TRUE. Now apply the same logical test to the above data with OR condition.
We will have result in either FALSE or TRUE. Here we got True.
Drag the Formula to other cells.
Now, look at the difference between AND & OR functions. OR returns TRUE for students B & C even though they have scored less than 35 in one of the exams. Since they score more than 35 is Marks 2, OR function found the condition of >35 TRUE in 2 logical tests and returned TRUE as the result.
#2 – IF Logical Function in Excel
IF excel Function is one of the important Logical Function to discuss in excel. IF includes 3 arguments to supply. Now, look at the syntax.
- Logical Test: It is nothing but our conditional test.
- Value if True: If the above logical test in excel is TRUE what should be the result.
- Values if FALSE: If the above logical test in excel is FALSE what should be the result.
For an example take look at the below data.
Now if the price of the product is more than 80 then we need the result as “Costly”, if the price of the product is less than 80 then we need the result as “Ok”.
Step 1: Here the logical test is whether the price is >80 or not. So open the IF condition first.
Step 2: Now pass the logical test in excel i.e. Price >80.
Step 3: If the logical test in excel is TRUE we need the result as “Costly”. So in the next argument i.e. VALUE if TRUE mention the result in double quotes as “Costly.
Step 4: Final argument is if the logical test in excel is FALSE. If the test is false then we need the result as “Ok”.
We got the result as Costly.
Step 5: Drag the formula to other cells to have resulted in all the cells.
Since the price of the products Orange and Sapota is less than 80 we got the result as “Ok”. Logical test in excel is >80, so we got “Costly” for Apple and Grapes because their price is > 80.
#3 – IF with AND & OR Logical Functions in Excel
IF with other two logical functions (AND & OR) one of the best combination formulas in excel. For better understanding take a look at the below example data which we have used for AND & OR conditions.
If the student scored more than 35 is both the exams then we will declare him as PASS or else FAIL.
AND by default can only return TRUE or FALSE as the result. But here we need the results as PASS or FAIL. So we have to use IF condition here.
Open the IF condition first.
If can only test one condition at a time, but here we need to look at two conditions at a time. So open AND condition and pass the tests as Exam 1 >35 and Exam 2>35.
If both the supplied conditions are TRUE we need the result as PASS. So mention the value PASS if the logical test in excel is TRUE.
If the logical test in excel is FALSE then the result should be Fail.
So, here we got the result as Pass.
Drag the Formula to other cells.
So, instead of default TRUE or FALSE, we got our own values with the help of the IF condition. Similarly, we can apply OR function as well, just replace AND function with OR function.
Things to Remember
- AND function requires all the logical tests in excel to be TRUE.
- OR function requires at least any one of the logical tests to be TRUE.
- We have other logical tests in excel like IFERROR function, NOT function, TRUE Function, FALSE etc.…
- We will discuss the remaining Excel logical tests in a separate article.
Recommended Articles
This has been a guide to Logical Tests in Excel. Here we discuss Logical Functions like AND, OR and IF in Excel along with practical examples and a downloadable template. You may learn more about excel from the following articles –
- How to use SUMIF with Multiple Criteria?
- IFERROR in Excel VBA
- Using SWITCH in Excel
- SUMPRODUCT in Excel with Multiple Criteria
- VLOOKUP Function with SUM
- VLookup Function with IF
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
