How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Wednesday

Defining Explicit Precedence in Google Sheets Query (Logical Operators)

Posted by   on

We must use parentheses to define explicit precedence when using logical operators AND, OR, and NOT in Google Sheets Query.


When using more than one logical operator in Query to join multiple conditions, we must use parentheses to define explicit precedence.


Otherwise, the formula won’t return the correct information that we want.


Here my sample data contains eight records that spread across seven columns. I want to retrieve the item codes (column A) based on the below conditions.


Sample Records in Sheets

Filter records if:


1. Either column B = “Yes” or column C = “Opened” (Condition 1 [highlighted in Cyan color on the image below])


And;


2. F = TRUE (Condition 2 [highlighted in Green])


3. G = TRUE (Condition 3 [highlighted in Yellow])


Here is the correct formula that will return the required output.


=QUERY(A1:G, "select A where (B='Yes' or C='Opened') and F=TRUE and G=TRUE")

Formula Example - Defining Explicit Precedence in Query

In the above example, all three conditions are met in rows 2, 3, and 9.


The parentheses around the OR operators are important.


If we want, we can use them around the AND also as show in the image above.


Here is an alternative formula that will return the same records:


=QUERY(A1:G, "select A where F=TRUE and G=TRUE and (B='Yes' or C='Opened')")

What Happens When I Do Not Define Explicit Precedence in Google Sheets Query?


Assume we have removed the parenthesis around the OR operators as below.


=QUERY(A1:G, "select A where B='Yes' or C='Opened' and F=TRUE and G=TRUE")

It will produce a different result because the query is evaluated in the following order.


Filter records if:


1. Column B = “Yes”


Or;


2. Column C = “Opened,” and Column F = TRUE, and Column G = TRUE.


Query Formula without Parentheses around Logical Operators

In the first example above, you can find an “alternative formula”. In that formula also, I have used parenthesis to define explicit precedence.


What happens when we remove them?


Formula:


=QUERY(A1:G, "select A where F=TRUE and G=TRUE and B='Yes' or C='Opened'")

This query will evaluate in the following order:


Filter records if:


1. Column F = TRUE, and Column G = TRUE, and Column B = “Yes”


Or;


2. Column C = “Opened”.


So the result will be the item codes in rows 2, 3, 4, 7, 8, and 9.


That’s all. Thanks for the stay. Enjoy!


Query Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!