Mastering the OR formula is key to analyzing data in Google Sheets. This definitive guide will teach you how to use the OR function to test multiple conditions and get TRUE if any are met. With step-by-step instructions, examples of OR formulas, combining with IF statements, and comparing the free alternative WPS Spreadsheets, you’ll learn how to leverage OR to query your data more effectively.
What Is the OR Function in Google Sheets?
The OR function adheres to a straightforward syntax, making it easy to grasp and implement. Its structure can be summarized as follows:
OR(logical_expression1, [logical_expression2], ...)
Here's a breakdown of the components:
logical_expression1: Represents the first condition to be evaluated.
[logical_expression2]: Represents an optional second condition to be evaluated.
...: Indicates that additional conditions can be included, separated by commas.
Here are some things to note about the OR function:
- It can take up to 254 logical arguments
- The arguments can be references to cells, comparisons using =, <>, <, etc="" or="" other="" functions="" that="" return="" true="" false="">
- If any argument evaluates to TRUE, the OR function returns TRUE
- If all arguments evaluate to FALSE, the OR function returns FALSE
In simple terms, the OR function runs through each logical test and sees if there is at least one TRUE result. If yes, the overall result is TRUE. If all are FALSE, the final output is FALSE.
How to Use the OR Function in Google Sheets
Using the OR function in Google Sheets is straightforward once you understand the syntax. If you want to execute a function in a specific cell, follow these steps:
Step 1: Click on the cell where you want to execute the function.
Step 2: Type in "=OR(" and write your first argument.
Step 3: Add a comma and write your second argument.
Step 4: Close the function by adding a closing bracket ")" and press Enter.
And that's it! The OR function will return TRUE if any logic argument equates to TRUE.
Examples of Using OR Function in Google Sheets
The OR function finds applications in a diverse range of scenarios, streamlining data analysis and decision-making. Here are a couple of illustrative examples:
Example 1: Status checking
Suppose you have a list of customer orders and you want to identify orders that are either shipped or completed. You can use the OR function to combine the two conditions and return a list of matching orders.
In cell C2, enter the following formula:
=OR(B2="Shipped",B2="Completed")
This formula checks the value in cell B2 and returns TRUE if it is either "Shipped" or "Completed". Otherwise, it returns FALSE.
The OR function takes two or more logical expressions as arguments and returns TRUE if any of the expressions are TRUE. In this example, the OR function checks two expressions:
B2="Shipped": This expression checks if the value in cell B2 is equal to "Shipped".
B2="Completed": This expression checks if the value in cell B2 is equal to "Completed".
Example 2: Identify students
The OR function can be used with multiple conditions to create more complex expressions. For instance, you could use the following formula to identify students who are either eligible for financial aid, have a GPA of 3.5 or higher, and are enrolled in at least 15 credits:
In cell E2, enter the following formula:
=OR(B2="Yes",C2>=3.5,D2>=15)
This formula checks three conditions:
B2="Yes": This expression checks if the value in cell C2 is equal to "Yes", indicating that the student is eligible for financial aid.
C2>=3.5: This expression checks if the value in cell B2 is greater than or equal to 3.5, indicating that the student has a GPA of 3.5 or higher.
D2>=15: This expression checks if the value in cell E2 is greater than or equal to 15, indicating that the student is enrolled in at least 15 credits.
If any of these conditions are TRUE, then the OR function will return TRUE, indicating that the student meets the criteria. Otherwise, the OR function will return FALSE.
How to Use the OR with IF Function in Google Sheets
Nested functions with IF and OR are a technique used to combine multiple logical conditions into a single expression. This is useful for creating complex decision-making rules.
An IF statement is a simple tool used to test logic. It requires three pieces of information: a logical test, a value to return for a true test, and a value to return for a false test. The OR function takes two or more arguments and returns true if at least one of them is true. The AND function takes two or more arguments and only returns true if all of them are true.
Example 1: Ranking numbers
Let's say you have a table with two columns: "Product" (column A) and "Sales" (column B). You want to categorize the products as either "High," "Medium," or "Low" based on their sales, and you have specific criteria for each category.
In cell C2 (assuming your table starts from row 2), you can use the following formula:
=IF(OR(B2>150, AND(B2>100, A2="C")), "High", IF(OR(B2>80, AND(B2>50, A2="D")), "Medium", "Low"))
Let's break down this formula:
The outermost IF statement checks if the sales are greater than 150 or if the sales are greater than 100 and the product is "C." If true, it assigns the category "High."
If the outermost IF statement is false, it moves to the next IF statement, which checks if the sales are greater than 80 or if the sales are greater than 50 and the product is "D." If true, it assigns the category "Medium."
If the outermost and the second IF statements are false, it assigns the category "Low."
Example 2: Grading score
Imagine you have a table in Google Sheets with the following data:
In this table, you want to populate the "Result" column based on the "Score" column. You have the following criteria:
If the score is greater than or equal to 90, the grade is "A."
If the score is between 80 and 89 (inclusive), the grade is "B."
If the score is between 70 and 79 (inclusive), the grade is "C."
If the score is below 70, the grade is "F."
Now, you want to consider additional conditions. If a student's score is 95 or above, they receive a bonus point, indicated by "Excellent," and if the score is 75 or below, they receive a warning, indicated by "Needs improvement."
Let's use nested IF, AND & OR statements to achieve this:
In cell C2 (Result for Alice), you can use the following formula:
=IF(OR(B2>=95, AND(B2>=90, B2<=94)),>
IF(AND(B2>=80, B2<=89),>
IF(AND(B2>=70, B2<=79),>
IF(B2<70,>
Breaking down the formula:
The outermost IF checks if the score is 95 or above (OR) in the range of 90-94. If true, it assigns "A (Excellent)."
If the outermost IF is false, it checks if the score is between 80 and 89, assigning "B" if true.
If the second IF is false, it checks if the score is between 70 and 79, assigning "C" if true.
If none of the above conditions are met, it assigns "F (Needs improvement)."
Drag this formula down for the other rows, and you'll get the corresponding grades for each student.
Best Free Alternative to Google Sheets - WPS Spreadsheet
WPS Spreadsheet is a part of WPS Office, a great free alternative to Microsoft Office with full compatibility. Here are some of its advantages:
Compatibility: WPS Spreadsheet is fully compatible with office suites like Microsoft Excel, Google Sheets, LibreOffice Calc, and OpenOffice Calc.
File Formats: Supports common file formats including .xls, .xlsx, .xlsm, .xlt, and .csv.
Platform Accessibility: Edit Excel files on various devices such as PC Windows, Mac, Linux, Android, and iOS.
Additional Features: Includes features like merge and center cells, highlight duplicates, insert checkbox, batch rename files, invoice maker tool, and easy formulas.
Extended Compatibility: Besides PC Windows, Mac, Android, and iOS devices, WPS Spreadsheet extends compatibility to Linux, a feature not covered by Microsoft Excel.
For home users and small businesses, WPS Office provides all the essential features you need for documents, spreadsheets, and presentations – all at no cost. The smooth compatibility, light footprint, and reliability make it a great choice over paid options.
How to Download WPS Office for Free?
Downloading and installing WPS Office takes just a few minutes:
Step 1: Go to the official WPS Office website: https://www.wps.com/.
Step 2: Click on Free Download.
Step 3: The installation file will be downloaded. Once the download is complete, double-click on the file to run it.
Step 4: Click “Install Now” to guide you through the WPS Office installation process. Enable the default options.
Step 5: After installing WPS Office, you can easily launch its apps from the Start menu, just like any other program.
WPS will allow you to fully open, edit, and create Excel-compatible spreadsheets while offering a lean, free office suite.
FAQs
How many conditions should I use in an OR function?
Ideally, keep OR functions to 2-4 logical conditions for clear logic. Too many can get confusing. Evaluate your data scenarios and what realistically would be evaluated as TRUE vs FALSE.
Can I use AND and OR together?
Yes, you can nest AND and OR to create complex conditional logic. Take care to properly structure and order the functions to avoid errors.
Summary
The OR function allows easily testing multiple conditions in Google Sheets, returning TRUE if any statement is correct. This guide explained the syntax, provided examples, and showed how to pair with IF to output custom values from the logic. For a free alternative, WPS Office has fully Sheets-compatible spreadsheets while being lightweight and easy to use anywhere. With tools like the OR function and WPS Spreadsheets, you can better analyze data.
