What Is Logical Test In Google Sheets?
Logical test in Google sheets, as the name suggests, logically tests whether the data satisfies the given condition or not. Some of the logical tests in Google sheets can be performed using AND, OR and IF functions in Google sheets.
For example, consider the below table showing marks obtained by students in Physics subject.
Now, let us use the AND function to test the data logically and find the result.
The steps are:
To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D2. Next, select the cell where we want to find the result. In this example, it is cell D2. Next, insert the AND function formula in Google sheets. So, the formula is =AND(B2>50,C2>50). Press Enter key.
We will be able to see the result in cell D2 as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
In this article, let us learn how to use the logical test in Google sheets with detailed examples.
Table of Contents
Key Takeaways
- Logical test in Google sheets is used to logically find the result based on the condition.
- Usually, users can apply the functions used such as AND Function, OR Function, and IF Functions in both Excel and Google sheets to test data logically.
- Users must remember while using multiple functions, we need to enter the correct number of parentheses in the formula.
- We need to note that, the AND, OR and IF functions are inbuilt functions, available by default in Google sheets.
How To Use Logical Test In Google Sheets?
The functions which we can use to logically test the values in Google sheets are
- AND Function
- OR Function
- IF Function
AND Function
The formula of AND function is =AND(logical_expression1, [logical_expression2], …)
where
- logical_expression1 is the mandatory argument showing the data to be tested using the AND function.
- Logical_expression2 is the optional argument and it is the additional values to be tested logically in the AND function.
OR Function
The formula of OR function is =OR(logical_expression1, [logical_expression2], …)
where
- logical_expression1 is the mandatory argument showing the data to be tested using the OR function.
- Logical_expression2 is the optional argument and it is the additional values to be tested logically in the OR function.
IF Function
The formula of AND function is =IF(logical_expression, value_if_true, value_if_false)
where
- logical_expression is the mandatory argument showing the data to be tested using the IF function.
- value_if_true is the mandatory argument and it is the condition to be returned if the value satisfies the condition.
- value_if_false is the optional argument and it is the condition to be returned if the value does not satisfy the condition.
#1 – AND & OR Logical Test In Google Sheets
Using AND & OR logical test in Google sheets can help users find the logical test. Here, we use a combination of both AND & OR functions in Google sheets.
For example, consider the below table showing products along with sales in first and second quarters.
Now, let us use the AND and OR function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D3. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the AND and OR function formula in Google sheets.
So, the formula is =AND(OR(B2>=100),OR(C2>=200))
Step 3: Press Enter key.
We will be able to see the result in cell D2.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
#2 – IF Logical Test In Google Sheets
It is the most common logical test in Google sheets used to find if the value logically satisfies the given condition or not.
For example, consider the below table showing marks obtained by various students in Biology.
Now, let us use the IF function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:C4. Next, select the cell where we want to find the result. In this example, it is cell C2.
Step 2: Next, insert the IF function formula in Google sheets.
So, the formula is =IF(B2>40,”PASS”,”FAIL”)
Step 3: Press Enter key.
We will be able to see the result in cell C2.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
#3 – IF With AND & OR Logical Test In Google Sheets
This is a combination of all 3 logical functions used in a single formula in Google sheets.
For example, consider the below table showing marks obtained by various students in years 1,2 and 3, respectively.
Now, let us use the IF function with AND & OR functions to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D4. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the IF function with OR and AND formula in Google sheets.
So, the formula is =IF(OR(B2>40,C2>40),AND(B2>40,C2>40),”PASS”)
Step 3: Press Enter key.
We will be able to see the result in cell D2.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Examples
Example #1 – AND Logical Test In Google Sheets
For example, consider the below table showing marks obtained by various students in semesters 1 and 2.
Now, let us use the AND function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D4. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the AND function formula in Google sheets.
So, the formula is =AND(B2>50,C2>50).
Step 3: Press Enter key.
We will be able to see the result in cell as shown in the below image.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Example #2 – OR Logical Test In Google Sheets
For understanding, let us use the same example used for AND function. Consider the below table showing marks obtained by various students in semesters 1 and 2.
Now, let us use the OR function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:E4. Next, select the cell where we want to find the result. In this example, it is cell E2.
Step 2: Next, insert the OR function formula in Google sheets.
So, the formula is =OR(B2>50,C2>50)
Step 3: Press Enter key.
We will be able to see the result in cell as shown in the below image.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Example #3 – AND & OR Logical Tests In Google Sheets
For example, consider the below table showing sample products and values in columns A and B respectively.
Now, let us use the AND & OR function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D3. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the AND & OR functions formula in Google sheets.
So, the formula is =AND(OR(B2>=20),OR(C2>=15))
Step 3: Press Enter key.
We will be able to see the result in cell as shown in the below image.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Example #4 – IF Logical Test In Google Sheets
For example, consider the below table showing marks obtained by various students in Physics subject.
Now, let us use the IF function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:C4. Next, select the cell where we want to find the result. In this example, it is cell C2.
Step 2: Next, insert the IF function formula in Google sheets.
So, the formula is =IF(B2>30,”PASS”,”FAIL”)
Step 3: Press Enter key.
We will be able to see the result in cell as shown in the below image.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Example #5 – IF With AND & OR Tests In Google Sheets
For understanding, let us use the same example used for IF function. Consider the below table showing marks obtained by various students.
Now, let us use the AND function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D4. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the IF function with OR and AND formula in Google sheets.
So, the formula is =IF(OR(B2>30,C2>30),AND(B2>30,C2>30),”PASS”)
Step 3: Press Enter key.
We will be able to see the result in cell as shown in the below image.
Now, using the Auto Fill option, we can find the result in other cells as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
Important Things To Note
- Logical test comprises of IF, AND and OR functions.
- The formula of AND function is =AND(logical_expression1, [logical_expression2], …)
- The formula of OR function is =OR(logical_expression1, [logical_expression2], …)
- The formula of AND function is =IF(logical_expression, value_if_true, value_if_false)
Frequently Asked Questions
For example, consider the below table showing marks obtained by various students.
Now, let us use the AND function to test the data logically and find the result.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is located in the cell range A1:D2. Next, select the cell where we want to find the result. In this example, it is cell D2.
Step 2: Next, insert the AND function formula in Google sheets.
So, the formula is
Step 3: Press Enter key.
We will be able to see the result in cell D2 as shown in the below image.
Likewise, we can find the result logically using logical test in Google sheets.
We need to remember to add the right number of parenthesis in the formula to make sure the function work correctly.
Also, make sure to enter the arguments for each function, accordingly.
We may get #NUM! error while using the logical tests with IF, OR & AND functions in Google sheets if we don’t input the right number of parenthesis, for each function.
Download Template
This article must help understand Logical Test In Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Logical Test In Google Sheets. Here we explain how to use Logical Test In Google Sheets using IF, OR, AND functions with its examples. You can learn more from the following articles.
Leave a Reply