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 criteriaMatch Multiple CriteriaCriteria based calculations in excel are performed by logical functions. To match single criteria, we can use IF logical condition, having to perform multiple tests, we can use nested IF conditions. But for matching multiple criteria to arrive at a single result is a complex criterion-based calculation.read more 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.

Logical Test

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.

You can download this Logical Functions Excel Template here – Logical Functions Excel Template

#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.

Logical AND Example 1

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

  1. Open AND function first.

    Logical AND Example 1-1

  2. First logical 1 is Marks 1 is >35 or not. To test the condition.

    Logical AND Example 1-2

  3. The second test is Marks 2 is >35 or not. So apply the logical test.

    Logical AND Example 1-3

  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.

    Logical AND Example 1-4
    Drag the Formula to the rest of the cells.

    Logical AND Example 1-5
    In cell D3 & D4, we have got FALSE as the result because in Marks 1, both the students scored less than 35.

    Logical AND Example 1-6

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.

Logical OR Example 1-7

We will have results in either FALSE or TRUE. Here we got True.

Logical OR Example 1-8

Drag the formula to other cells.

Logical OR Example 1-8

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.

Logical IF Example 2-7

#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 IF Example 2
  • 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.

Logical IF Example 2

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.

Logical IF Example 2-1

Step 2: Now pass the logical test in excel, i.e., Price >80.

Logical IF Example 2-2

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.

Logical IF Example 2-3

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.”

Logical IF Example 2-4

We got the result as Costly.

Logical IF Example 2-5

Step 5: Drag the formula to other cells to have resulted in all the cells.

Logical IF Example 2-6

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.

Logical IF Example 2-7

#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.

logical Test in excel Example 3

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.

logical Test in excel Example 3-1

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.

logical Test in excel Example 3-2

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.

logical Test in excel Example 3-3

If the logical test in excel is FALSE, then the result should be Fail.

logical Test in excel Example 3-4

So, here we got the result as Pass.

logical Test in excel Example 3-5

Drag the formula to other cells.

logical Test in excel 3-6

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.

logical Test in excel Example 3-7

Things to Remember

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>