Updated July 5, 2023
What is OR in Excel?
The function OR in Excel is structured as =OR(Condition1, Condition2,…) and performs logical tests by checking if any of the given conditions are true. If at least one of the conditions is true, the function returns TRUE; otherwise, it returns FALSE.
This feature makes it extremely helpful for various applications, such as checking if criteria are met or testing whether a value falls within a certain range.
We can employ OR functions in data analysis, engineering, and financial modeling. It is a popular choice among professionals who rely on Excel for complex calculations and decision-making, as it is flexible and simple to work with.
For example, the formula =OR(B6=300,C6=”Yes”) tests the two conditions:
- Daily Target Achieved >=300
- Monthly Target Achieved = “Yes”
If the given values satisfy either of the two conditions, the OR function provides a value TRUE or else FALSE.
Syntax of OR in Excel
The syntax for the OR function is
Arguments for the Syntax
- [logical1] (Required) – The first condition we want the OR function to evaluate.
- [logical2]…..[logical30] (Optional) – The function can evaluate additional conditions. The OR function supports up to 30 conditions in a single formula.
Key Highlights
- The function OR evaluates multiple logical conditions and returns a single result for all the conditions.
- Users can Nest OR function within another logical function, such as IF or AND, to create more complex logical tests.
- Additionally, we can combine the OR function with other Boolean operators, such as NOT and AND, to create more advanced logical tests.
- If users provide invalid arguments to the OR function, it will return a #VALUE!
How to use OR in Excel?
To use the function OR in Excel worksheets, follow these steps:
- Create a column where you want to display the test results.
- Enter the OR formula: =OR(Condition1,Condition2,…).
- Press the Enter key to display the result.
Here is an example to demonstrate these steps in detail:
Testing Two Conditions with Numeric Values
Example #1:
The table below displays students’ names and their marks in History and Psychology. We will use the OR function to check whether the given data satisfies the following conditions.
- Condition 1: Marks in History are equal to 10
- Condition 2: Marks in Psychology are equal to 20
Solution:
Step 1: Create a new column with Status as the header to display the result.
Step 2: Type the OR formula =OR(B6=10, C6=20) in cell D6.
Explanation: The formula =OR(B6=10, C6=20) checks whether the value in cell B6 equals 10 or the value in cell C6 equals 20. If either of these conditions is met, it returns TRUE; otherwise, it returns FALSE.
Step 3: Press Enter key to view the result.
Step 4: Drag the formula into other cells to test the conditions for the remaining cells.
Testing Two Conditions with Numeric and Text Values
Example #2
The below table shows a list of class V students with their total marks and percentages. We want to test if a student’s percentage satisfies the below two conditions or not using the function OR in Excel.
- Condition 1: The Student’s Name is equal to Samuel
- Condition 2: Percentage is equal to 40
Solution:
Step 1: Enter the formula =OR(A6=“Samuel”, C6=40) in cell D6
Explanation: The OR function checks if the value in cell A6 is Samuel and that in C6 is 40.
Step 2: Press Enter key to view the result
Explanation: The OR function finds that the value in cell A6 is Samuel, but the value in cell C6 is not 40. The OR function provides TRUE results since the given data satisfies one of the two conditions.
Step 3: To apply the given condition to other cells, drag the formula to the remaining cells to obtain the desired output.
Testing Three Conditions with Numeric and Text Values
Example #3
Consider the same example as above. Here, we want to test the 3 conditions using the OR function.
- Condition 1: The Student’s Name is equal to Samuel
- Condition 2: Percentage is greater than 40
- Condition 3: Percentage is greater than 60
Solution:
Step 1: Write the values in columns A, B, and C.
Step 2: Type the formula in cell D6 =OR(A6=”Samuel”, C6>40, C6>60)
Explanation:
The OR function checks if the value in cell A6 is Samuel, C6 is greater than 40, and if C6 is also greater than 60.
Step 3: Click Enter key to view the result.
The function finds that given data satisfies the first and second conditions but does not satisfy the third condition. Since out of three conditions, the data satisfies at least two conditions; the OR function provides the result TRUE.
Step 4: To apply the given condition to other cells, drag the formula to the remaining cells to obtain the desired output.
Testing Three Conditions With a Nested OR or Nested AND Within the IF Function
Example #4
The below table shows a list of customer IDs, products purchased, and quantities. We want to check if the quantity sold satisfies the given conditions:
- Condition 1: Qty is greater than 10
- Condition 2: Qty is less than 5 or equal to 7
- Condition 3: Qty is between 5 and 10
Solution:
Step 1: Write the values (Qty) in column C.
Step 2: Enter the formula =IF(C6>10, “C6 is greater than 10”, IF(OR(C6<5, C6=7), “C6 is less than 5 or equal to 7”, “C6 is between 5 and 10”))
Explanation:
- The formula checks the first condition to determine whether C6(Qty) exceeds 10; in such cases, it returns “Qty is greater than 10“.
- Alternatively, it evaluates the second condition using the OR function to check whether C6 (Qty) is less than 5 or equal to 7.
- If either of these conditions is TRUE, the formula returns “Qty is less than 5 or equal to 7“.
- If both conditions are FALSE, the formula returns “Qty is between 5 and 10“.
Step 3: Press Enter key to get the result
Step 4: Drag the formula to the remaining cells to apply the given conditions and obtain the below result.
Things to remember while using OR Function
- The OR function accepts up to 255 arguments, including logical values, references to cells containing logical values, or arrays of logical values.
- Ensuring that cell references in the OR function contain logical values (i.e., TRUE or FALSE) is important, as non-logical values can lead to unexpected results.
- When using the OR function with the AND function, it’s important to use parentheses to ensure the correct order of operations.
- Considering the order in which the OR function tests the conditions is important, as the OR function stops evaluating further conditions as soon as it receives a TRUE value.
Frequently Asked Questions (FAQs)
Q1) How do you write OR function in Excel?
Answer: To write the function OR in Excel, you can use the OR function formula and provide the logical values or expressions as arguments within the parentheses. For example, to check if either B1 or C1 contains the value “Yes”, you can write the OR function as “=OR(B1= “Yes”, C1= “Yes”)”.
Q2) What kind of function is OR in Excel?
Answer: The OR function in Excel is a logical function that checks whether at least one of the given conditions is true. A logical function is a tool that checks whether a statement is true or false. It’s like a mini-quiz that Excel can automatically grade for you. We can use OR with other logical functions, such as IF, to create more complex formulas and perform more advanced calculations in Excel.
Q3) Can I use if and or together in Excel?
Answer: You can use the IF and OR function to test a logical condition. For example, if we want to determine whether a student is eligible for a scholarship, we will write the formula below.
=IF(OR(B6>=150,C6>=150),”Eligible”,”Not Eligible”)
The OR and IF function check if a student has scored greater than or equal to 150 marks in either of the two classes, then they are Eligible for a scholarship; otherwise, they are Not Eligible.
Q4) Can I combine and/or in Excel?
Answer: Yes, it is possible to use Excel’s AND and OR functions to evaluate conditions for multiple data sets. For instance, you can use the following formula to verify if a salesperson has made sales over $1000 each day from 01 January 2023 to 08 January 2023:
=OR(AND(B6>1000,B6<1500,C6>DATE(2023,1,1)),A6=$D$5)
AND: The function checks if Aaron has achieved more than 1000 and less than 1500 sales. Also, the function checks if the salesperson has achieved sales from 01 January 2023 onwards.
OR: The OR function, in combination with the AND function, checks if Aaron has achieved sales between 1000 and 1500 on each day from 01 January 2023 to 08 January 2023 and provides a value- TRUE.
Recommended Articles
The above article is a guide to the OR Function in Excel. Here, we explain the use of OR Function, real-world examples, and Excel templates for you to practice while learning. You can also go through our other suggested articles –
