How to Use Logical Functions in Google Sheets: AND, OR, NOT

September 14th, 2023

Logical functions are the building blocks of decision-making within spreadsheets. These functions enable your Google Sheets to evaluate conditions and make decisions based on them. This article introduces you to three primary logical functions: AND, OR, and NOT.

AND

The AND function checks multiple conditions and returns TRUE if all conditions are met, and FALSE otherwise.

Example:

Consider you have a list of students with their math and science scores. You want to flag those who scored above 80 in both subjects.

=AND(B2>80, C2>80)

In the above example, if the student scored above 80 in both math (cell B2) and science (cell C2), the function will return TRUE.

OR

The OR function checks multiple conditions and returns TRUE if at least one of the conditions is met. If none of the conditions are met, it returns FALSE.

Example:

Using the same list of students, you want to identify those who scored above 80 in either math or science.

=OR(B2>80, C2>80)

Here, if the student scored above 80 in either math or science, the function will return TRUE.

NOT

The NOT function returns the opposite of a condition. If the condition is TRUE, it will return FALSE, and vice versa.

Example:

You have a cell (A1) that either says "Pass" or "Fail". You want another cell to display TRUE if A1 does NOT say "Pass".

=NOT(A1="Pass")

If A1 contains "Pass", the function will return FALSE. For any other value, it will return TRUE.

Combining Logical Functions

The real power of logical functions becomes evident when you start combining them.

Example:

Using the student's list again, you want to flag those who scored above 80 in either subject but didn't score above 90 in both.

=AND(OR(B2>80, C2>80), NOT(AND(B2>90, C2>90)))

This formula checks if the student scored above 80 in either subject and ensures they didn't get above 90 in both.

Conclusion

Understanding and utilizing logical functions in Google Sheets empowers you to make dynamic, decision-driven spreadsheets. As you familiarize yourself with AND, OR, and NOT, you'll discover countless applications that enhance data analysis and streamline tasks.