The XOR function returns a logical Exclusive OR of all arguments, i.e. the XOR function returns TRUE if an odd number of the supplied conditions evaluate to TRUE, and returns FALSE otherwise.
- This function was introduced in Excel 2013 and so is not available in earlier versions.
=XOR(logical_test-1,[logical_test-2] … )
|logical_test-1||Conditions to evaluate as TRUE or FALSE, and can be logical values, arrays, or references|
|[logical_test-2] …||Optional. Up to 254 conditions you want to evaluate as either TRUE or FALSE|
- If an array or reference argument contains text or empty cells, those values are ignored
- You can use an XOR array formula to see if a value occurs in an array. To enter an array formula, press Ctrl+Shift+Enter
- The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even
|1||Data A||Data B||Notes|
|7||=XOR(A2>B2,A2<B4)||FALSE||Evaluates to TRUE:TRUE – both tests evaluate as true, FALSE is returned|
|8||=XOR(A2>B4,B3>A3)||FALSE||Evaluates to FALSE:FALSE – both tests evaluate as false, FALSE is returned|
|9||=XOR(A4>B2,B3>A3)||TRUE||Evaluates to TRUE:FALSE – only one test evaluates to true, TRUE is returned|
|10||=XOR(A4>B2,A2>B2,0>3,0>2)||FALSE||Evaluates to TRUE:TRUE:FALSE:FALSE – number of TRUE inputs is even, FALSE is returned|
|11||=XOR(A4>B2,A2>B2,3>0,0>2)||TRUE||Evaluates to TRUE:TRUE:TRUE:FALSE – number of TRUE inputs is odd, TRUE is returned|
Note: The Exclusive OR logical operation returns TRUE if one (and only one) of two or more supplied conditions evaluate to TRUE. It can be thought of as “either A or B, but not both A and B”.
More generally, when there is just one condition or when there are more than two conditions, the Exclusive OR operation evaluates to TRUE if an odd number of conditions evaluate to TRUE.
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if the specified range contains no logical values|