This guide covers everything you need to know about the Google Sheets OR function, including its definition, syntax, use cases, and how to use it.
What is the OR Function? How Does It Work?
The OR function is a logical operator in Google Sheets that returns a Boolean value as a result. This Boolean value is determined by evaluating whether any of the provided arguments are logically true. If any argument is true, the OR function will return the Boolean value TRUE. If all arguments are false, the function will return FALSE.
The purpose of the OR function is to test multiple conditions within your spreadsheet and to return a true or false outcome based on these conditions. This is especially useful when dealing with large datasets where manual inspection would be time-consuming or impractical.
The function can evaluate both individual logical expressions and ranges of data. Logical expressions can be simple equality checks such as “A2 = ‘foo'”, referencing a cell value, or more complex calculations that can be interpreted as TRUE or FALSE.
A crucial detail about the OR function is that numerical values are also interpreted as logical values. Specifically, 0 is interpreted as logically false while all other numbers (including negative ones) are treated as TRUE.
As an example, the function OR(A2 = “foo”, A3 = “bar”) checks whether cell A2 contains the string “foo” or cell A3 contains “bar”. If either of these conditions is true, the function will return TRUE. If neither condition is met, it will return FALSE.
OR Syntax
The syntax and arguments for the function are as follows:
OR(logical_expression1, [logical_expression2, …])
Where:
- logical_expression1: This is a mandatory argument. It is an expression or reference to a cell containing an expression that represents some logical value, i.e., TRUE or FALSE, or an expression that can be coerced to a logical value.
- logical_expression2, …: These are optional arguments. They are additional expressions or references to cells containing expressions representing some logical values, i.e., TRUE or FALSE, or expressions that can be coerced to logical values.
Here are some important syntax and usage notes:
- The OR function takes at least one argument, but if you want to check more conditions, you can add more arguments. It can accommodate a maximum of 255 arguments.
- Text values can be used in logical expressions. The text value “TRUE” (case insensitive) is considered logically true, while the text value “FALSE” (case insensitive) and all other text values are considered logically false.
- If any argument is an error, the OR function will return an error.
- The OR function in Google Sheets is not case-sensitive. For example, OR(“TRUE”, “false”) and OR(“true”, “FALSE”) will yield the same result.
Examples of How to Use the OR Function
Here are some practical examples of how to use the OR function in Google Sheets.
Example #1: Basic Usage of the OR Function
Suppose you have a list of students and their test scores for two subjects: Math and English. You want to know if any student has scored above 90 on either of the tests. In this case, you can use the OR function.
Let’s say the Math scores are in column B, and the English scores are in column C. You can use the OR function in D2 cell as follows:
=OR(B2>90, C2>90)
This formula will return TRUE if the student scored more than 90 in either Math or English and FALSE if not.
Example #2: Using the OR Function with Text
The OR function can also be used with text. Suppose you have a list of employees and their departments. You want to check if an employee belongs to either the “Marketing” or “Sales” department. If the departments are listed in column B, you can use the OR function like this:
=OR(B2=”Marketing”, B2=”Sales”)
This formula will return TRUE if the employee belongs to either the “Marketing” or “Sales” department and FALSE if not.
OR: Common Mistakes & Problems
When using the OR function in Google Sheets, there are several common mistakes and problems that users often encounter. These include:
- Incorrectly formatted arguments: The OR function requires at least one logical expression to work properly. You’ll receive an error message if you try to use it without any arguments. Make sure each argument is a valid logical expression, such as “A1>5”.
- Exceeding the maximum number of arguments: Google Sheets allows you to use up to 255 arguments in the OR function. You’ll encounter an error if you try to exceed this limit.
- Using text values without quotation marks: If you’re comparing a cell value to a text string, make sure to put the text string in quotation marks. For example, use “B2=’Yes'” instead of “B2=Yes”.
- Forgetting about blank cells: By default, the OR function treats blank cells as FALSE. If you want to treat them as TRUE, you’ll need to include an additional argument to check if the cell is not empty, such as “A1<>””.
Why Is OR Not Working? Troubleshooting Common Errors
If you’re having trouble using the OR function in Google Sheets, it’s crucial to identify the errors you’re coming across. These errors can be as simple as syntax errors or as complex as data type mismatches. This section will help you identify common errors, their causes, and how to resolve them.
#VALUE! Error
Cause: When you encounter the #VALUE! error, it generally means that the OR function is trying to evaluate a cell that contains non-logical values. This can occur if you include other non-logical data types in your function.
Solution: To fix this, you should check the cells that you’ve referenced in your OR function. Make sure all cells only contain logical values (TRUE or FALSE) or an expression that evaluates to a logical value. If you find non-logical values, either remove them or convert them into logical values.
#N/A Error
Cause: The #N/A error usually appears when the OR function cannot find the values it is supposed to evaluate. This could be because the referenced cells are empty or because the cell reference itself is incorrect.
Solution: Double-check the cell references in your OR function. Ensure that the cells you’re referencing are not empty and that the cell references are correct. If you find any empty cells, fill them with the appropriate logical values.
#REF! Error
Cause: The #REF! error is typically a sign that your OR function refers to a cell that doesn’t exist. This can happen if you’ve deleted a row or column that was referenced in your function or if you’ve entered a cell reference that falls outside the spreadsheet grid.
Solution: To resolve this, revisit your OR function and ensure all referred cells exist in your spreadsheet. If you’ve deleted a row or column, restore it or update your function to reflect the current state of your spreadsheet. If your cell reference falls outside the spreadsheet grid, correct it to point to an existing cell.
#NAME? Error
Cause: The #NAME? error is displayed when Google Sheets cannot recognize the function name. This typically occurs if you’ve misspelled the OR function or used a function that doesn’t exist.
Solution: Check your spelling and syntax. The OR function should be typed in all capital letters and followed by a pair of parentheses. If you’ve used a non-existent function, replace it with a valid one.
#DIV/0! Error
Cause: The #DIV/0! error indicates that your OR function is trying to divide by zero, which is mathematically undefined. This error could occur if you’re using the OR function in combination with other functions that result in a division by zero.
Solution: Review the other functions used in combination with the OR function. If any of them result in a division by zero, modify them to avoid this mathematically undefined operation.
Using OR With Other Google Sheets Functions
Combining the OR function with other Google Sheets functions can make your spreadsheets even more powerful and versatile. Here are some examples of how OR can be used in conjunction with other functions.
With IF
The OR function can be used within the IF function to test multiple conditions simultaneously. If any of the conditions are true, the IF function will return a true value.
Example:
Let’s say you have a class of students and want to determine if any of these students have scored more than 80 in either math or science. You have the math scores in column B and the science scores in column C. In cell D1, you can use this formula:
=IF(OR(B1>80, C1>80), “Yes”, “No”)
This formula will return “Yes” if the student scored more than 80 in either subject and “No” otherwise.
For more details on the OR function, check out the official documentation at the Google Docs Editors Help Center.
