In this tutorial, we will elaborate OR function in Excel and indicate example formula using it. Along with you will learn how to use it together with other functions where OR function can be nested.
What is OR function in Excel?
The OR function of excel is a logical function used to return TRUE if any argument is TRUE and return FALSE if the value of all the arguments is evaluated as FALSE.
In addition, this is used to avoid nested ifs in the logical test and also can be combined in AND function. Basically, this function is used to check more than logical conditions at the same time and can take up 255 conditions in single formula arguments.
Technically, the expression in each argument should return TRUE or FALSE or if evaluated the value can TRUE or FALSE. Meanwhile, in arguments you can input constants, cell references, arrays, or logical expressions.
- logical1 – This is required argument and first condition or logical value to evaluate.
- logical2 – This is the second logical value to evaluate, it is optional.
=OR(logical1, [logical2], ...)
Returns TRUE if any argument is TRUE, otherwise FALSE.
How to use OR function in Excel
As a worksheet function, it can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider the following examples:
For instance, we want to test if the value in Cell A2 or the value in B2 is greater than 75. To do this use the formula as follows:
Additionally, we can use this function, and extend functionality with other functions. For instance IF Function, so we will use the same formula above to test this formula together with IF.
=If(OR(A2>75,B2>75), "Pass", "Fail")
This formula will return “Pass” if the value in A2 is greater than 75 OR the value in B2 is greater than 75.
Let’s take a look at how we can test a few conditions. Assume we have the following data:
Below is the result using the OR function:
Here is the explanation of the above example:
So the function in cell G2 evaluates as TRUE because both conditions are FALSE. Meanwhile, on cell G3, the result is True because both conditions are True. It goes the same on the next row of functions.
In this example, we enter the formula in array form definitely using OR function. Supposedly this will return true if any of the range of cells is greater than 75.
So to do this, Press CTRL + SHIFT + ENTER. This will add curly braces. However in Microsoft Excel 2021 and 365 you can just hit Enter to complete this, thus no curly braces are shown.
And now we will combine the OR function with the AND function.
Specifically, AND function will return TRUE if all the conditions are TRUE if not, return FALSE. So in our example, above AND function apparently has two arguments that are split with the OR function. This will return TRUE if the value in cell G2 is greater than or equal to 80, and if the range of B2 to E2 is greater than or equal to 75.
Excel OR function in IF
The other creative way to use OR function of Excel is with IF function. Oftentimes this function in nested by one of the popular functions IF. Along with this, it can create a customized result instead of TRUE or FALSE value.
The formula will create this way:
The logical statement…
“If G12 is greater than 80, or if H2 is equal to “A”, then display the word “Passed”. Otherwise, display Failed,”
…would be represented in Excel as follows:
Technically the If function allows to return specific value or mathematical calculation instead of default out TRUE if the argument evaluates TRUE.
Things to keep in mind in doing the formula are that text values must be enclosed in double quotes, but numerical values and mathematical formulas are entered without double quotes.
You can use in the third argument the IF function’s value_if_false, otherwise the FALSE value will be displayed on rows that failed the criteria.
Wrapping up OR function of Excel is helpful in executing tests within the single function which identifies cells that meet any one of multiple criteria.
Apparently, because the logic is easy it can be utilized in any type of dataset. The good thing is combining with other functions and features in Excel is easy and adaptable.
Thank you for reading 🙂