If you have been using Google Sheets for some time, you must be familiar with some of its features such as the Pivot Table, the Slicer, and Conditional Formatting. Today, we will tackle the logical operators in Google Sheets.
If you tried mastering the basics of computer programming, logical operators are considered foundational knowledge and are included in every crash course in programming.
Logical operators are fundamental because they allow computers to do rudimentary decision-making. For example, even a relatively simple process of sorting orders by geographical region requires logical operators to sort them correctly.
This guide will help you master the basics of logical operators, and use it to enhance your master of Google Sheets.
The Logical Functions: NOT(), AND(), OR()
Logical operators test a certain set of conditions, triggering a certain event once those set of conditions are met (or not met). There are three basic logical operators: the NOT, the OR, and the AND operator. Google Sheets implement them as NOT(), OR(), and AND() functions, respectively.
The NOT() function functions like the NOT logical operator: it flips the value from TRUE to FALSE or from FALSE to TRUE.
The AND() function checks two cells if both are TRUE or not; if both cells are TRUE, then the AND() function outputs a TRUE value; else the output is FALSE.
The OR() function outputs a TRUE value if at least one of two cells it references hold a TRUE value; if both cells contain FALSE value, then its output is FALSE.
The table below summarizes the results of the listed logical functions above given the various combinations of TRUE and FALSE values.
While the NOT() function only works with a single cell as a reference, both AND() and OR() functions also work even if the input comes from more than two cells. The same rules apply:
The AND() function will output a FALSE value if at least one of the cells in the reference array contains a FALSE value.
The OR() function will output a TRUE value if at least one of the cells in the reference array contains a TRUE value.
Combining Logical Functions with Comparison Operators
To further appreciate the power of logical functions, we will try combining them with comparison operators, which are used to compare two values and check if a certain condition is fulfilled.
Comparison operators include the equal to operator, the greater than operator, and the less than operator. The table below lists the comparison operators, their corresponding symbols, and the equivalent functions.
The comparison operators, when used as part of a formula, outputs either a TRUE or FALSE value. This behavior allows comparison operators to be combined well with the logical functions. For example, we want to compare two numbers on different cells. To use the comparison operator, we simply follow the following format:
The table below shows how the comparison operators work.
The three logical functions also allow formulas containing comparison operators or functions as input. To see how it works, let us have a set of examples combining logical functions with comparison operators.
Checking whether both of the two requirements are satisfied
The AND() function is useful for finding out if two requirements are satisfied. For example, we want to check if the gross profit exceeds a certain amount and that the total number of orders is less than a certain benchmark amount. To do so, we write the following formula:
And the result looks like this:
Checking whether at least one of the two requirements are satisfied
The OR() function is useful for finding out if one of the two requirements are satisfied. For the same example as above, we want to check if either the gross profit exceeds a certain amount or that the total number of orders is less than a certain benchmark amount. To do so, we write the following formula:
And the result looks like this:
Combining Logical Functions with Conditional Formulas
We'll complete this tutorial by combining the logical functions with conditional formulas IF() and IFS().
Using IF() function
We want to spice up our example of checking whether the two requirements are satisfied by coding it such that the cell will display “Benchmark satisfied :)” if both requirements are satisfied; else the cell will display “Benchmark not satisfied :(“. The formula will look like this:
Note this portion:
This portion is the condition being checked by the IF() function, with its result used to do whatever action corresponds to the resulting value.
The result looks like this:
Using IFS() function
The IFS() function allows you to check if at least one of the several conditions are satisfied and implement the corresponding formula. We wish to fully upgrade our benchmark-checking sheet by also including a way to check what requirement is not satisfied. To do so, we need to list down what should happen for every possible scenario:
If both requirements are satisfied, print “Benchmarks satisfied :)”. To do so, we use the following condition:
If the gross profit requirement is satisfied but the maximum total orders requirement is not satisfied, print “Total order amount exceeds benchmark”. To do so we use the following condition:
If the gross profit requirement is not satisfied but the maximum total orders requirement is satisfied, print “Gross profit below benchmark”. To do so we use the following condition:
If both the gross profit requirement and the maximum total orders requirement are not satisfied, print “Both benchmarks not satisfied :(“. To do so we use the following condition: