Unlike the OR function which returns TRUE if only 1 condition is TRUE, the XOR function returns TRUE if the number of TRUE conditions is ODD and FALSE if the number of TRUE Conditions is EVEN.
Like the OR Function, at least one of the test conditions must evaluate to TRUE to return TRUE. XOR function will return FALSE if all conditions evaluate to FALSE
=OR( 2=2, 2>3, 4>3 )=TRUE this is because at least 1 condition is TRUE
=XOR( 2=2, 2>3, 4>3 )=FALSE this is because the number of TRUE Conditions is EVEN
If no logical conditions are found, XOR returns #VALUE error
Interestingly, the XOR function can be represented by the minus (-) Sign
(2=2) – (2>3) – (4>3) => FALSE
(2>2) – (2>3) – (4>3) => TRUE
How is this possible?
In Excel, the numeric equivalent of a FALSE Boolean value is Zero (0) while any other number (positive or negative) is equivalent to a TRUE Boolean Value.
(2=2) – (2>3) – (4>3) => TRUE – FALSE – TRUE ==> 1- 0 -1 ===> 0 ( FALSE )
(2>2) – (2>3) – (4>3) => FALSE – FALSE – TRUE ==> 0- 0 -1 ===> -1 (TRUE )
NB: One major difference between the minus (-) symbol and the XOR function is that the XOR function is not able to Spill logically!
FILTER Function Using XOR logic
Since the FILTER function has a spill capability, we shall use it to show the difference between the minus (- ) sign and the XOR function
=FILTER(Supplier[Name],
XOR(ISNUMBER(Supplier[1st pay]),ISNUMBER(Supplier[2nd pay]))
) ….Since XOR does not spill then the function result to CALC# error
=FILTER(Supplier[Name],
ISNUMBER(Supplier[1st pay])-ISNUMBER(Supplier[2nd pay]))…this works since the minus sign is able to spill
IF Function Using XOR logic
If you are checking single records instead of arrays, both XOR and Minus (-) sign works the same
NOTE:
XOR function can be used to test for Empty and non-empty cells
=IF(XOR(Supplier3[@[1st pay]:[2nd pay]]), TRUE, FALSE)
Above function will return:
- #VALUE error if all cells are empty
- TRUE if the number of non-empty cells are ODD
- FALSE if the number of non-empty cells are EVEN
COUNTIF Function Using XOR logic
COUNTIF function does not allow array calculations but there is a walk-around
Count all the suppliers paid only 1 time
=SUM(
ABS(
ISNUMBER(pays[1st pay])-ISNUMBER(pays[2nd pay] ) ) )
SUMIF Function Using XOR logic
Just like the COUNTIF function, SUMIF does not allow array calculations but there is a walk-around
=SUM(
ABS(ISNUMBER(tblpay[1st pay])-ISNUMBER(tblpay[2nd pay])) * tblpay[Sales] )
Good stuff. But 1 question:
where there are 2 TRUE & 1 FALSE results, = 1+1+0=2, i.e., TRUE (since result is not 0).
But you said that if TRUES are even, then XOR returns FALSE.
Please resolve this.
Good stuff. But 1 question:
where there are 2 TRUE & 1 FALSE results, = 1+1+0=2, i.e., TRUE (since result is not 0).
But you said that if TRUES are even, then XOR returns FALSE.
Please resolve this.
Hi Sandeep,
XOR logic uses the minus (-) sign not the Plus sign (+)
Therefore =1-1-0 = 0
EVEN true values = false
ODD true values = true