Logical Operators in Google Sheets

What are Logical Operators in Google Sheets?

Logical operators in Google Sheets are used to perform evaluation tests and make decisions based on those results. They help us in the evaluation of specific conditions and display results based on the outcome of those evaluations. There are three basic logical operators, which are the NOT, the OR, and the AND operator, in Google Sheets. They can be used in Google Sheets in the form of functions NOT(), OR(), and AND() functions, respectively. IF and IFS are other functions that can be used for logical evaluations.

In the example below, we have two numbers in a spreadsheet. After evaluating them, let’s display an output.

Here, we have used an IF statement, which evaluates the values in A! and BI to be greater than 10 and 20, respectively. If both conditions are satisfied, it will display TRUE; otherwise, it will display FALSE.

List of Logical Operators in Google Sheets

Some of the important logical operators used in Google Sheets are given below. Let us look at each of them in turn.

#1 – IF()

The IF function in Google Sheets is one of the main conditional statements. It checks a condition and returns a value if true (like “Yes”) and another value if false (like “No”). 

The syntax is as follows:

=IF(condition, value_if_true, value_if_false).

• condition is a statement that is either true or false.
• value_if_true: value that the function returns if the logical expression is TRUE
• value_if_false: value that the function returns if the logical evaluation is FALSE.

For example, we have two values in cells A1 and B1. Let us compare them and present the output. Here, the values in cells A1 and B1 are compared, and the output is printed accordingly.

#2 – NOT()

NOT function in Google Sheets reverses the Boolean value of the tested condition. If the condition is TRUE, NOT makes it FALSE, and vice versa.

• NOT(TRUE) returns FALSE
• NOT(FALSE) returns TRUE

The syntax is as follows:

NOT(logical_expression)

• logical_expression – An expression or reference to a cell holding an expression that represents some logical value, i.e., TRUE or FALSE.

Let us look at an example. Using the same numbers as above, let us check the value in cell A1.

For example, =NOT(A1 > 25)

Here, A1 is not greater than 25 and is evaluated as FALSE. However, since it reverses the Boolean value, we get TRUE.

#3 – AND()

The AND function in Google Sheets returns TRUE if all the conditions specified in it are true; else, it returns FALSE. The AND function in Google Sheets is a logical function used to check whether multiple conditions are true. If any of the expressions are evaluated as FALSE, the function returns FALSE.

The syntax for the AND function is as follows:

AND(logicalexpression1, [logicalexpression2, …])

• logicalexpression1 is the first condition to test.
• logicalexpression2, … (optional) are additional conditions to test. You can enter up to 255 conditions.

Let us look at an example.

We have two cells with text in a spreadsheet. Let us use the AND operator to check if they are fruits.

AND(A1 = “Apple”, B1 = “mango”) will return TRUE only if both conditions are satisfied.

Step 1: Enter the above formula in cell A3.

= AND(A1 = “Apple”, B1 = “mango”).

As both evaluate to TRUE, we get TRUE.

You can observe that they are case insensitive.

Step 2: Now, let us change one of the values and check the result. Change B1 to “grapes.”

The output is evaluated as FALSE as neither condition is satisfied.

#4 – OR()

The OR function returns TRUE if at least one of the conditions specified is true. If none of the conditions are true, then only FALSE will be returned. Let us look at an example. We have three values in a spreadsheet. We are checking three conditions. If any of the above holds TRUE, we get true.

Syntax

OR(logical_expression1, [logical_expression2, …])

• logical_expression1 – An expression that represents some logical value, i.e., TRUE or FALSE.
• Logical_expression2, … (optional) are additional conditions to test. You can enter up to 255 conditions.

The OR function returns true if any of the arguments are logically true, as seen above. Let us make all three false and check the results.

Examples

Let’s look at some logical operators in Google Sheets examples to learn how to apply them in different scenarios to derive the result.

Example #1 – Multiple IFs

Multiple Ifs are a handy feature provided by Google Sheets. They can be used as Google Sheets Nested IF statements, which allow you to test multiple conditions sequentially. Below is an interesting example of how to use them to check for multiple conditions.

We have some students’ grades. We will assign them a letter grade based on their numeric grades.

Now, to evaluate the grades of these students, we apply the following formula in cell E2.

Step 1: First, we calculate the average. Apply the following formula in cell E2 and drag it down to E6 to calculate their averages.

=AVERAGE(B2,C2, D2)

Step 2: Apply the following formula in cell F2.

=IF(E1 >= 90, “A”, IF(E1 >= 70, “B”, IF(E1 >= 60, “C”, IF(E1 < 60, “D”, “Absent”)))),

Step 3: Press Enter and drag the formula to cell F6 to get the grades of all the students.

Explanation:

=IF(E1 >= 90, “A”, IF(E1 >= 70, “B”, IF(E1 >= 60, “C”, IF(E1 < 60, “D”, “Absent”))))

1. The first outermost IF checks if the average is greater than 90. If so, it gives the A grade. Otherwise, it checks if the average is greater than 70(but less than 90) here. 

IF(E1 >= 70, “B”,

2. If so, it displays B. Otherwise, it moves to the third IF which checks if the average is greater than 60. If so, it gives you a C grade.

IF(E1 >= 60, “C”, 

3. The final innermost IF checks if the value is less than 60. If so, it assigns D. If none of the conditions are satisfied, it means the student was absent.

IF(E1 < 60, “D”, “Absent”))))

Example #2 – IF with AND

Combining IF with AND

As seen above, handling multiple Ifs can get a little confusing unless you are well versed in it. You can use AND functions with IF to handle multiple conditions simultaneously. We check the student’s attendance percentage and marks to determine whether he passed or failed that academic year. Look at the table below. 

Step 1: In the table below,0

• Column A stands for attendance percentage (must be at least 80%)
• Column B is the exam score (it must be at least 50% to pass)

Step 2: Use the following combination of IF and AND to check if the student passes.

Enter the following formula in C2.

=IF(AND(A1 >=80, B1 >= 50), “Pass”, “Fail”)

Step 3: Press Enter. You get the result.

Step 4: Drag the formula to cell C4 for all the results.

Here, unless both the AND conditions are met and TRUE, the student is declared as FAIL. 

Example #3 – IF with Or

Let us use the example where you can apply a discount based on the purchased item. Here, if the item is apple or orange, you get a 10% discount. 

The purchased item is in Column A. You apply a 10% discount to apples or oranges; for the others, no discount is applied.

Step 1: To calculate the final price for these values after applying the correct discount, you can use the following formula in cell C2.

=IF(OR(A2 = “Orange”, A2 = “apple”), B2 – (0.1*B2), B2)

Explanation

• OR(A2 = “Orange”, A2 = “apple”) checks if the item in column A is an apple or orange. 
• B2 – (0.1*B2), – If true, it applies a 10% discount (i.e., multiplies by 0.1) and subtracts the discount from the total amount to get the reduced price.
• B2 – If not an apple or orange, the original amount is printed.

Step 2: Press Enter. You get the final amount.

As it is an apple, a 10% discount has been applied. Now, drag the formula to cell C7 for the other items.

You can see the discount applied to either apples or oranges.

Example #4 – IF with AND and OR

Let us see a simple example where we combine AND and OR with IF. Given below is a table containing details of 10 employees. If the employee has good conduct and a salary greater than $3000 or experience greater than four years, he gets a bumper bonus; otherwise, he gets only a fair bonus. Let us implement this in Google Sheets.

Step 1: Apply the following formula in cell D2.

=IF(AND(B2 = “Good” , OR(C2 > 3000, D2 > 4)), “Bumper Bonus”, “Small Bonus”)

Explanation: This formula returns a “Bumper Bonus” if B2 is “Good” and either C2 is greater than 3000 or D2 is greater than 4; otherwise, it returns a “Small Bonus.”

Step 2: Press Enter and drag the formula to D11 to get the results for all the employees.

Important Things To Note

• Logical operators in Google Sheets can be used in conditional formatting to highlight cells based on specific conditions.
• They can be used in array formulas to apply conditions for an entire range at once. E.g.,
• =ARRAYFORMULA(IF(A1:A5 > 50, “Pass”, “Fail”)) applies the IF condition to the range A1:A5.
• SWITCH can be used as an alternative to multiple Ifs.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Logical Operators 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 Operators in Google Sheets. Here we learn the list of logical operators and their uses with step-wise examples. You can learn more from the following articles –

Formula Parse Error in Google Sheets

FLOOR Function Google Sheets

Calendar In Google Sheets