WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Logical Test in Excel

Logical Test in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

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

Step 1: Open AND function first.

Logical AND Example 1-1

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

Logical AND Example 1-2

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

Logical AND Example 1-3

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

  • 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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Logical Functions Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More