Excel OR Function is a logical function that tests one or more logical conditions at a time and returns TRUE if at least one condition is TRUE, but returns FALSE if all the conditions are FALSE. For example, to test the number in cell A1 is greater than 10 OR less than 20, we write the Excel OR function as =OR(A1>10, A1<20). The OR function can be used in the IF function and SUMIF function, further, it can also be combined with the AND function. We Shall see in detail about the Syntax, arguments, and Output of the Excel OR function along with details about some common Errors encountered.
Syntax of Excel OR Function
=OR (logical1, [logical2], ...)
Arguments of OR Function
Logical conditions are passed as arguments to the OR function. A logical condition is an expression that returns TRUE or FALSE, or its a value that can be evaluated as TRUE or FALSE. The arguments provided to the OR function can be constants, cell references, arrays, or logical expressions. The maximum number of arguments that Excel OR function can take is 255.
logical1 – It is the first condition or logical value to evaluate
logical2 – It is the Second condition or logical value to evaluate. It is Optional.
Return Value or Output of Excel OR Function
The OR function returns TRUE if atleast one of the arguments evaluate to TRUE, but will return FALSE if all of the arguments evaluate to FALSE.
Output of OR Function with numbers as arguments
The OR function returns TRUE for all numbers except 0 (zero), for which it returns FALSE. It returns TRUE even for all decimals, and negative numbers.
=OR(1) => TRUE
=OR(-234.244) => TRUE
=OR(1/4) => TRUE
=OR(0) => FALSE
Examples
For example, to test if the value in A1 OR the value in B1 is greater than 100, use the following formula:
=OR(A1>100,B1>100)
OR can be used to extend the functionality of functions like the IF function. You can supply OR as the logical_test for an IF function like the below:
=IF(OR(A1>60,B1>75), “Pass”, “Fail”)
The above formula will return “Pass” if the value in A1 is greater than 60 OR the value in B1 is greater than 75.
Array formula to OR Function
If you enter OR as an array formula, you can test all values in a range against a condition. For example, this array formula will return TRUE if any cell in A1:A100 is greater than 50:
=OR(A1:A100>50)
Excel OR Function ERRORS
The OR function will return #VALUE Error in cases when no arguments are provided to it or when a blank cell reference is provided as an argument. This Error explains itself as “The Value used in the formula is of the Wrong data type “.
#Name? Error or Invalid Name Error is seen when text is provided as an argument. This error means that unrecognized text is provided as an argument. If the text is enclosed in a double quotes then it becomes a #VALUE Error.
How to use the Excel OR Function
The OR function requires at least one logical condition to operate. It can test the validity of multiple conditions at the same time, however, the maximum number of logical conditions that an Excel OR function can test is 255.
Things to remember about Excel OR Function
- The OR function is not case-sensitive.
- The maximum number of arguments the OR function can take are 255.
- The OR function does not support wildcards.
- The OR function will return #VALUE error when the arguments are blanks or blank cell references and will return a #NAME? error when the argument is plain text.