OR Function in Excel

The OR function of Excel is used to test multiple conditions at the same time. A condition is an expression that evaluates to either “true” or “false” but not to both simultaneously. The OR function is categorized as a Logical function of Excel.

It returns either of the following outcomes:

• True, if any of the conditions is true or all the conditions are true
• False, if all the conditions are false

For example, the formula “=OR(2>4,8>5)” (ignore the double quotation marks) returns “true.” The first condition (2>4) evaluates to false, while the second condition (8>5) evaluates to true. Since one of the conditions is true, the final output is “true.”

A condition is supplied as an argument to the OR function. An argument can consist of numbers, text strings, cell references, formulas or Boolean values (true and false). The purpose of using the OR function is to assess whether any of the various conditions is met or not. It also facilitates multiple comparisons of data values.

The syntax of the OR function in excel is given in the following image:

The OR function accepts the following arguments:

• Logical1: This is the first condition to be tested.
• Logical2: This is the second condition to be tested.

“Logical1” is required, while “logical2” and the subsequent “logical” arguments are optional.

The OR function can be used either alone or in combination with the IF function of Excel. The benefits of using the OR function are expanded when it is combined with the AND and IF functions of Excel. For such combinations, the OR and/or AND functions can be nested within the IF function.

Let us consider some examples to understand the working of the OR function of Excel. To know more about the nested OR function, refer to example #4 of this article.

Note 1: Each argument (condition) of the OR function must necessarily evaluate to either “true” or “false.” Moreover, each condition should consist of the relevant logical (or comparison) operator. There are six logical operators in Excel. These are “equal to” (=), “not equal to” (<>), “greater than” (>), “greater than or equal to” (>=), “less than” (<), and “less than or equal to” (<=).

Note 2: A maximum of 255 conditions can be tested in Excel 2007 and the newer versions. In the earlier versions of Excel, a total of 30 conditions can be tested.

Example #1–Test Two Conditions Containing Numeric Values

The succeeding image shows a number and the name of an unavailable website in cells A1 and A2 respectively. With the help of the OR function of Excel, evaluate the following conditions together:

• The value of cell A1 is less than 10
• The value of cell A1 is equal to 40

The steps to test the given conditions by using the OR function in excel are listed as follows:

Step 1: Insert the required logical operators in the given conditions. So, enter the following formula in cell C1.

“=OR(A1<10,A1=40)”

Here, the first condition is A1<10 and the second condition is A1=40.

Step 2: Press the “Enter” key. The output in cell C1 is “false.” This is because both the given conditions are false. Neither the value of cell A1 is less than 10, nor is it equal to 40.

The formula and the output are shown in the following image.

Example #2–Test Two Conditions Containing a mix of Numerical and Textual Values

Working on the dataset of example #1, we want to evaluate the following conditions together:

• The value of cell A1 is equal to 45
• The value of cell A2 is “Google.”

Use the OR function of Excel.

The steps to test the given conditions with the help of the OR function are listed as follows:

Step 1: Enter the following formula in cell C1.

“=OR(A1=45,A2=“Google”)”

We have inserted the appropriate logical operators in the given conditions. So, the first condition is A1=45 and the second condition is A2=“Google.”

Step 2: Press the “Enter” key. The output appears as “true” in cell C1.

In this example, the first condition is true as the value of cell A1 is equal to 45. The second condition is false as the value of cell A2 is not “Google.” Since one of the two conditions evaluate to true, the final output is “true.”

The formula and the output are shown in the following image.

Example #3–Test Three Conditions Containing a mix of Numerical and Textual Values

Working on the dataset of example #1, evaluate the following three conditions together:

• The value of cell A1 is greater than or equal to 5
• The value of cell A1 is equal to 35
• The value of cell A2 is “e2esols.com.”

Use the OR function of Excel.

The steps to test the given conditions by using the OR function in excel are listed as follows:

Step 1: Insert the logical operators that fit the given conditions. So, enter the following formula in cell C1.

“=OR(A1>=5,A1=35,A2=“e2esols.com”)”

Here, the first, second, and third conditions are “A1>=5,” “A1=35,” and A2=“e2esols.com” respectively.

Step 2: Press the “Enter” key. The output in cell C1 is “true.”

The first condition evaluates to true because 45 (value of cell A1) is certainly greater than 5. The second condition is false as 45 is not equal to 35. The third condition is true as the value of cell A2 is “e2esols.com.”

So, two out of three conditions are true. Consequently, the final output is also “true.” Even if one of the three conditions had been true, the final output would still have been “true.”

The formula (entered in step 1) and the output are shown in the following image.

Example #4–Test Three Conditions With a Nested OR or Nested AND Within the IF Function

The succeeding table shows some random numbers in column A. In column B, the following three conditions are tested:

• The value of cell A2 is less than 50
• The value of cell A3 is not equal to 75
• The value of cell A4 is greater than or equal to 100

The preceding conditions have been tested by using the following functions (in the given sequence):

• OR function
• AND function
• IF and OR functions
• IF and AND functions

Column C shows the outputs obtained by each formula of column B. The row numbers are displayed under the label “Sr. No,” which stands for serial number.

Consider each column and row of the table as the respective column and row of an Excel worksheet.

The conditions tested in column B and their outcomes are listed as follows:

• A2<50: It evaluates to false as the value of cell A2 (50) is not less than 50.
• A3<>75: It evaluates to true because the value of cell A3 (25) is not equal to 75.
• A4>=100: It evaluates to false as the value of cell A4 (99) is neither greater than nor equal to 100.

The rows of column B are explained as follows:

Row 2: In this row, the three preceding conditions are tested (in cell B2) using the OR excel function. Since one of the three conditions is true, the final output of the OR function (in cell C2) is “true.”

Row 3: In this row, the three preceding conditions are tested (in cell B3) using the AND function. Since two out of three conditions are false, the final output of the AND function (in cell C3) is “false.”

Row 4: In this row, the given conditions are evaluated (in cell B4) using the nested OR function. Since the output of the OR function is “true” (as explained for row 2), the IF function considers that the logical test (condition) has been met. Consequently, the IF function returns the string defined as the “value_if_true” argument, which is “data error” (in cell C4).

Row 5: In this row, the three stated conditions are evaluated (in cell B5) using the nested AND function. The AND function returns the output “false” (as explained for row 3). So, the IF function realizes that the logical test has not been met. As a result, the IF function returns the string “data correct” (in cell C5), which is the “value_if_false” argument.

Note 1: The AND function returns “true” if all the tested conditions evaluate to true. It returns “false” if any of the conditions evaluate to false. Like the OR function, the AND function is also a logical function of Excel.

Note 2: In nesting functions, one function is placed inside another function. The function that is inside is known as a nested function. Excel calculates the nested function first, followed by the outer function. The output of the nested function is used as an argument of the outer function.