Logical Test in Excel

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.

What is the 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 often than not, in excel, we use a logical test to match multiple criteria 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 if 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, the 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 the 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. To test the condition.

Step 3: The 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 the formula by default returns TRUE or else returns FALSE as a result.

Drag the Formula to the rest of the 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 the 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 results 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, the OR function found the condition of >35 TRUE in 2 logical tests and returned TRUE as a result.

#2 – IF Logical Function in Excel

IF excel Function is one of the important Logical Functions 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 example, take a 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 mentions 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.” The 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 the 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 in both the exams, then we will declare him as PASS or else FAIL.

AND, by default, can only return TRUE or FALSE as a result. But here we need the results as PASS or FAIL. So we have to use the IF condition here.

Open the IF condition first.

If we 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 the OR function as well, just replace the AND function with the 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 the IFERROR function in excel, NOT function in excel, TRUE Function in excel, 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 –

• SUMIF with Multiple Criteria
• IFERROR in Excel VBA
• SUMPRODUCT in Excel with Multiple Criteria
• VLookup Function with IF