OR Function
It is one of the logical functions that acts opposite to the AND function
What is the OR Function?
The OR function in Excel returns TRUE if at least one condition is TRUE; otherwise, it returns FALSE.
Many a time when you are hungry, you face a dilemma of whether to go to MacDonalds or the Wendys. On one side, you have the BigMac and the world-famous fries, and on the other side, you have the Baconator and the frosty that delights people of every age group.
Some may even prefer to go to Burger King. Of course, if you have a budget of $10 to $15, it would only be possible to go to one of those three places, but it's enough to satiate your hunger.
The example signifies that even though there are three different places to eat, going to either is enough to fill your empty stomach.
If you speak the same in Excel terms, input two or more conditions, and even if one of the conditions evaluates to TRUE(while the other to FALSE), you will ultimately get the result as TRUE.
The OR function works under precisely similar circumstances. It is one of the logical functions that acts opposite to the AND function.
This article will show the syntax, process, and some examples related to the function.
Key Takeaways
-
The OR function will evaluate TRUE when either input condition is fulfilled and only return as FALSE when all the conditions fail to satisfy.
-
On the contrary, the AND function will evaluate to TRUE only when all the conditions are fulfilled and FALSE when even one of the conditions fail to satisfy.
-
Apart from the required argument, you can also input 255 optional logical conditions as arguments in the function.
-
The formula's length in any logical conditions should not exceed 8000+ characters. For example, the string 'James works at Yahoo!' distance is equal to 21.
-
If you reference empty cells or cells containing text strings as logical conditions, then the function will return a #VALUE! Error.
-
The function does not support the use of wildcard characters.
-
The function is not case-sensitive, meaning it will recognize capitalized 'A' and lowercase 'a as the same.
Understanding the OR function
The OR is categorized as a logical function that returns TRUE even when one of the conditions is met and FALSE when all the conditions fail to satisfy.
The essence of the function is to enable the user to evaluate multiple conditions and find whether one of the conditions is fulfilled or all of them fail to comply with the given criterion.
Suppose you have three numbers as A=20, B=40, and C=10 in the spreadsheet.
We need to evaluate whether A is smaller than both B and C. As we said, the OR function allows us to write multiple conditions in a single cell.
In this case, we input the condition as OR(A<B, A<C). We know that 20 is less than 40, so that condition evaluates to TRUE. On the other hand, we also have the disease as 20 is less than 10, which is FALSE. What do you believe the result would be?
If you thought the final result would be TRUE, then you are right! Per the definition, even when one of the conditions evaluates to TRUE, the final result for the function would be TRUE.
Sometimes, you don't need the result as a boolean value. Here, you can use the IF statements to return a customized effect for either boolean value, i.e., TRUE or FALSE.
We did use the function to get the result, but you might have overlooked an essential component of our process - the comparison operators.
The logical operators play an essential role in the input of different criteria in the functions. Though insignificant, these operators play a great role when you combine them with numbers, text strings, and complex numerical expressions.
Listed below are the logical operators that you can use while using the function:
| Condition | Operator | Description |
|---|---|---|
| Equal to | = | Compares two values and returns TRUE if they are equal, |
| Not equal to | <> | Compares two values and returns TRUE if they are not equal |
| Greater than | > | If one value is greater than the other then the condition evaluates to TRUE |
| Less than | < | If one value is less than the other, then the condition evaluates to TRUE |
| Greater than or equal to | >= | The condition returns as TRUE if the value is greater than or equal to another value |
| Less than or equal to | <= | The condition returns as TRUE if the value is less than or equal to another value |
OR Function Formula
The syntax for the OR function:
=OR(logical1,[logical2])
where,
- logical1 = (required) The first logical condition which will be evaluated
- logical2 = (optional) The second logical condition which will be evaluated
You can use up to 255 additional optional logical statements in the function.
Note
Before we understand how to use the function, please remember that the function only evaluates to FALSE when all the given logical conditions fail to satisfy. Otherwise, the function would always evaluate to TRUE.
How to use the OR Function in Excel?
If you would have read most of our articles, we would like to repeat it - "There are two ways to use the function - from the functions library or as a worksheet formula."
In this section, we will explore both methods and let you decide what method suits you the best.
Method 1: From the function's library
If you have just begun your journey to becoming an Excel wizard, we advise you to use the functions library. Functions are the predefined formulas that let you input the arguments directly into text boxes and return the result in the select cell.
To use the functions, please follow the steps below:
Select the cell in which you intend to get the result.
Click on the Formulas tab > Logical drop-down in the Function Library and select the OR function.
This should open up the dialog box, as illustrated below:
As per the syntax, we will input conditional statements to evaluate whether the length of text strings 'Apple' and 'Orange' is equal to 5. Once you input both statements as separate arguments, the dialog box will look like
As you can already see, we get a preview of our final result, which equals TRUE. Even though our logical2, i.e., LEN("Orange")=5 evaluates to FALSE, our final result doesn't change since logical1 gives us the 'true' nature of our development.
Now, all you need to do is click on OK, and you will get the same result in the selected cell.
If you change the logical one to LEN("Pineapple")=5, we only get the result:
Method 2: As a worksheet formula
Using the functions as a worksheet formula is as easy as ABC. The only tricky part is remembering which function would work the best at a particular instance, and that's what we intend to help you accomplish through this article.
Suppose you have three numbers in the spreadsheet, as illustrated below:
We need to find whether either of the condition evaluates to TRUE. Here, we will use the formula =IF(OR(C3<C4, C3>C5), "One/Both of the conditions match!", "Neither condition is matching"), which should give the result:
Using the IF statement and the OR function, we were able to return a customized text string.
OR Function Examples
The good thing about the OR function is that you would find it helpful in many real-life work scenarios. It collaborates perfectly with its fellow functions to simplify Excel users' lives. In this section, we will see some examples of the process.
Example 1
Sometimes, your database might miss values in some columns. If all the columns compulsorily require a matter, you can use the combination of OR and IF to find the rows where you need to input the values.
Suppose that the data looks as illustrated below:
The general idea when you populate new data is that at least a new unique id, i.e., Employee Id would be assigned to an individual. We will ignore column A and check for missing data in the rest of the columns.
To find whether any rows are missing data, we will use the formula, =IF(OR(B3="",C3="",D3=""),"Data is missing",""), which will give us the result:
Whenever the data is missing in either column B, C, or D, we get the result 'Data is missing.' This way, we can sort the data based on the impact and input all the missing information into the database.
Example 2
Suppose you are an electronic goods store owner where you have made the sales, as illustrated below:
As a monthly performance indicator, if the sales in any of those regions are less than $90,000, you need to up your sales game. Additionally, the sales return should not exceed more than $4000.
To find the regions in which you need to concentrate on better advertising and customer retention, you can use the formula =IF(OR(C3<90000,D3>4000), "Please spend $" &C3*3.5%&" on advertising and misc", "Please spend $" &C3*2%&" on advertising and misc"), which gives the result:
The formula might look lengthy, but it mostly comprises of concatenations. We input two conditions, i.e., <90000 and >4000, for sales and sales return, respectively.
If either condition evaluates to TRUE, we take the sales amount and find what its 3.5% value is equal to, which should be the advertising cost in that particular region.
If both conditions fail to satisfy, the base rate of 2% is considered an advertising cost for the region.
Example 3
Suppose the teacher needs to determine which students from the class need to sign up for extra lectures. The data looks as illustrated below:
We need to work on the condition that if the student has scored less than 35 on either one of the tests, then they must sign up for the class.
The formula to find the students will be =IF(OR(C3<35,D3<35,E3<35), "Student needs to attend extra lectures"," "), which will give the result as
If you don't feel like an additional column in the database, you can use our favorite conditional formatting tool!
First, select the range B3:B12 and then click on Home > Conditional Formatting > New Rule, which should open up the dialog box, as illustrated below:
Here, we will use the formula as =OR(C3<35,D3<35,E3<35) set up the cell fill color that should look as:
Finally, click on Ok, which should give the result:
The only cells that would get highlighted are the ones that met either of the conditions that we had input in the conditional formatting tool using the formula.
OR vs. AND function
We had previously discussed how either function's purpose is to increase the scope of evaluation of the data based on two or more conditional expressions.
The AND function returns TRUE when all of its conditions are met while returns FALSE when either of the conditions fails to satisfy.
The syntax for the function is:
=AND(logical1,[logical2])
where
- logical1 = (required) The first logical condition which will be evaluated.
- logical2 = (optional) The second logical condition will be evaluated.
You can additionally use 255 optional logical conditions apart from logical1 that lets you accommodate additional criteria in data analysis.
Next, we will see an example to compare the result based on both functions.
Suppose you have the test scores for students as illustrated below:
AND function returns TRUE when all of its conditions are fulfilled. For example, let's say we need to find whether a student scored less than 40 on all their test scores.
To get the result, we will use the combination of AND and IF functions where the formula will be,
=IF(AND(C3<40,D3<40,E3<40),"All Subject scores are less than 40","")
To give the result:
As you can see, only Craig Schneider scored less than 40 on all three test scores.
The rest of the students fall under the following categories:
-
All three test scores are greater than 40
-
One test score is less than 40
-
Two test scores are less than 40
Thus, all three conditions must be met for the AND function to return the result as TRUE.
On the other hand, by using the formula =IF(OR(C3<40,D3<40,E3<40), "Score less than 40 on one or more test"," "), we get the result:
A rather contrasting result, whenever one of the conditions out of three evaluated as TRUE, the result that we get is 'Score less than 40 on one or more test'. On the other hand, only when three out of three conditions evaluate to FALSE we get an empty string.
Believe us; life would be much more convenient if you use the functions for number crunching or while making Excel models!
or Want to Sign up with your social account?