XOR Excel Function, introduced in Excel 2013, is a logical function that performs what is called an “Exclusive OR”. It tests two logical conditions at a time and returns TRUE if one condition is FALSE and the other is TRUE, however, XOR returns FALSE if both the conditions are TRUE. In case of more than two logical conditions, XOR returns TRUE if at least one condition is FALSE and an odd number of logical conditions evaluate to TRUE. The XOR 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 XOR function along with details about some common Errors encountered.
Syntax of Excel XOR Function
=XOR (logical1, [logical2], …)
Arguments of XOR Function
Logical conditions are passed as arguments to the XOR 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 XOR 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.
XOR vs OR Function
It can be said that XOR function performs what is called “Exclusive OR”, which is in contrast to the “Inclusive OR” performed by the Excel OR function.
OR function returns TRUE if at least one of the provided logical conditions is TRUE, and if all of the provided logical conditions evaluate to False, then only it gives FALSE as a result.
Key difference between OR & XOR is when all conditions are TRUE
When all the logical conditions evaluate to TRUE, the OR function returns TRUE, but XOR returns FALSE. Thus, it should be clearly understood that in order to get the result as TRUE with XOR function, at least any one of the provided logical tests must be FALSE. For the XOR function to return TRUE at least one of the logical conditions must be FALSE.
It should also be noted here that, when all conditions are FALSE, both OR and XOR Excel functions returns FALSE only.
Return Value of XOR Excel Function
XOR function returns TRUE or FALSE based on the conditions provided as input to it.
Output of XOR 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.
=XOR(1) => TRUE
=XOR(-234.244) => TRUE
=XOR(1/4) => TRUE
=XOR(0) => FALSE
Excel XOR Function ERRORS
The XOR 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.
Things to remember about Excel XOR Function
- With more than two logical conditions, XOR returns TRUE when the an odd number of logical conditions evaluate to TRUE and atleast one logical condition evaluating to FALSE.
- If all logical conditions are FALSE, then XOR returns FALSE.
- XOR function was introduced in Excel 2013.
- The Excel XOR function is not case-sensitive.
- The maximum number of arguments the XOR function can take are 255.
- The XOR function does not support wildcards.
- The XOR 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.