A logical test means having an analytical output that is either TRUE or FALSE. In Excel, we can perform a logical test for any situation. The most commonly used logical test is using the equals to the operator, which is “=” if 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 not easy to understand the concept of logical tests. But once you master this, it will be a valuable skill for 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. and arrive at the desired solution.
In Excel, we have as many as 9 logical formulas. We must go to the “Formulas” tab and click on the “Logical” function group to see the logical formulas.
Some of them are frequently used formulas, and some of them are rarely used. This article will cover some of the important Excel logical formulas in real-time examples. All the Excel logical formulas work based on TRUE or FALSE if the logical test we do.
Table of contents
- What is the Logical Test in Excel?
How to Use Logical Function in Excel?
Below are examples of logical functions in Excel.
#1 – AND & OR Logical Function in Excel
Excel AND and OR functions work opposite each other. For example, AND condition in Excel requires all the logical tests to be TRUE. On the other hand, the OR function requires any logical tests to be TRUE.
For example, look at the below examples.
We have student names, marks 1, and marks 2. If the student scored more than 35 in both exams, the result should be TRUE. If not, the 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
- We must open AND function first.
- The first logical 1 is Marks-1 is >35 or not to test the condition.
- The second test is Marks-2 is >35 or not. So, we must take the logical test.
- We have only two conditions to test. So, we have applied both the logical tests. Now close the bracket.
If both conditions are satisfied, the formula returns TRUE by default. Else, returns FALSE as a result.
Drag the formula to the rest of the cells.
In cells D3 and D4, we got FALSE because in “Marks-1,” both the students scored less than 35.
Example #2 – OR Logical Function in Excel
The Excel OR function is completely different from the AND function. The OR in ExcelOR In ExcelThe OR function in Excel is used to test various conditions, allowing you to compare two values or statements in Excel. If at least one of the arguments or conditions evaluates to TRUE, it will return TRUE. Similarly, if all of the arguments or conditions are FALSE, it will return FASLE. requires only one condition to be TRUE. Therefore, we must apply the same logical test to the above data with OR conditions.
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 and OR functions. The OR function returns TRUE for students B and C even though they have scored less than 35 in one of the exams. However, since they scored more than 35 in 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
The IF excel FunctionIF Excel FunctionIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. is one of the important logical functions to discuss in Excel. It includes three 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.
If the product’s price is more than 80, we need the result as “Costly.” On the other hand, if the product’s price is less than 80, we need the result as “OK.”
Step 1: Here, the logical test is whether the price is >80 or not. So, we must open the IF condition first.
Step 2: Now pass the logical test in Excel, Price >80.
Step 3: If the logical test in Excel is TRUE, we need the result as “Costly.” So in the next argument, VALUE, if TRUE, mentions the result in double-quotes as “Costly.”
Step 4: The final argument is if the logical test in Excel is FALSE. If the test is FALSE, we need the result to be “OK.”
We got the result of “Costly.”
Step 5: Drag the formula to other cells to have the result in all the cells.
Since the orange and sapota price is less than 80, we got the result as “OK.” However, the logical test in Excel is >80, so we got “Costly” for apples and grapes because their price is > 80.
#3 – IF with AND & OR Logical Functions in Excel
The IF function with the other two logical functions (AND & OR) is one of the best combination formulas in Excel. For better understanding, look at the below example data, which we have used for AND and OR conditions.
If the student scored more than 35 in both the exams, it would declare him a PASS or FAIL.
The AND function, 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 simultaneously, we need to look at two conditions. So, we must 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,” 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 values with the help of the IF condition. Similarly, we can also apply the OR function and replace the OR function with the IF and AND functions.
Things to Remember
- The AND functionFunctionThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE. requires all the logical tests in Excel to be TRUE.
- The OR function requires at least any logical tests to be TRUE.
- We have other logical tests in Excel like the IFERROR function in excelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error., NOT function in excelNOT Function In ExcelNOT Excel function is a logical function in Excel that is also known as a negation function and it negates the value returned by a function or the value returned by another logical function., TRUE Function in excelTRUE Function In ExcelIn Excel, the TRUE function is a logical function that is used by other conditional functions such as the IF function. If the condition is met, the output is true; if the conditions are not met, the output is false., FALSE, etc.…
- We will discuss the remaining Excel logical tests in a separate article.
This article is a guide to Logical Tests in Excel. We discuss logical functions like AND, OR, and IF in Excel, practical examples, and a downloadable template. You may also learn more about Excel from the following articles: –
- SUMIF with Multiple CriteriaSUMIF With Multiple CriteriaThe SUMIF (SUM+IF) with multiple criteria sums the cell values based on the conditions provided. The criteria are based on dates, numbers, and text. The SUMIF function works with a single criterion, while the SUMIFS function works with multiple criteria in excel.
- IFERROR in Excel VBAIFERROR In Excel VBAThe IFERROR function in Excel is used to determine what to do when an error occurs before performing any function.
- SUMPRODUCT in Excel with Multiple CriteriaSUMPRODUCT In Excel With Multiple CriteriaIn Excel, using SUMPRODUCT with several Criteria allows you to compare different arrays using multiple criteria.
- VLookup Function with IFVLookup Function With IFIn Excel, vlookup is a reference function, and IF is a conditional statement. Based on the results of the Vlookup function, they locate a value that meets the criteria and also matches the reference value.