XOR Function

[snd]

Hi to you all.
I'm new to Excel 2013.

This function puzzled me, I'll quote this from Excel Help:

"The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even." Then they gave an example:

[TABLE="class: banded flipColors"]
<thead>[TR]
[TD]Formula

[/TD]
[TD]Description
[/TD]
[TD]Result
[/TD]
[/TR]
</thead><tbody>[TR]
[TD]=XOR(3>0,2<9)
[/TD]
[TD]Because one of the two tests evaluates to True, TRUE is returned.
[/TD]
[TD]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

I applied it in an Excel cell and the result was "FALSE". Was their example wrong.

Thanks a lot.

 

[chicagocomputerclasses]

They made a mistake in their documentation

=XOR(3>0,2<9) probably should have been =XOR(3>0,2>9)

 

[snd]

Thanks chicagocomputerclasses.

 

[Rick Rothstein]

I'm new to Excel 2013.

This function puzzled me, I'll quote this from Excel Help:

"The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even." Then they gave an example:

[TABLE="class: banded flipColors"]
<thead>[TR]
[TD]Formula

[/TD]
[TD]Description
[/TD]
[TD]Result
[/TD]
[/TR]
</thead><tbody>[TR]
[TD]=XOR(3>0,2<9)
[/TD]
[TD]Because one of the two tests evaluates to True, TRUE is returned.
[/TD]
[TD]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

I applied it in an Excel cell and the result was "FALSE". Was their example wrong.

I cannot examine this for myself as XL2010 is the latest version of Excel that I have. My understanding of Xor (Exclusive Or) is as a logical operator that compares two logical expressions. In that form, its results can be summarized as Xor returns True if one but not both of the logical expressions are True, otherwise it returns False. Now you are saying Excel as introduced XOR as a function which can take more than two arguments. Fine, but they seemed to have extended the definition in an non-useful way. I could see the function being useful if it returned True when one, and only one, of its arguments were TRUE and FALSE otherwise, but I cannot think of any practical real-world uses for it when it returns TRUE when and odd number of its arguments are TRUE (except, of course, for when there are only two arguments).

It returns FALSE since you are providing an even amount of numbers. Had it been =XOR(10) or =XOR(10,8,5), it would return TRUE.

Just out of curiosity, what does it return for this...

=XOR(0,8,5)

I am thinking it should return FALSE because 0 is the numerical equivalent of FALSE. Does it?

 

[chicagocomputerclasses]

It treats any number other than 0 as true
0 is false

=XOR(1,10,5) TRUE
=XOR(1,1,5) TRUE
=XOR(0,1,5) FALSE
=XOR(0,0,5) TRUE

 

[Rick Rothstein]

It treats any number other than 0 as true
0 is false

=XOR(1,10,5) TRUE
=XOR(1,1,5) TRUE
=XOR(0,1,5) FALSE
=XOR(0,0,5) TRUE

Well, at least that is consistent. Thanks for checking it out for me.

 

[GTO]

... My understanding of Xor (Exclusive Or) is as a logical operator that compares two logical expressions. In that form, its results can be summarized as Xor returns True if one but not both of the logical expressions are True, otherwise it returns False. Now you are saying Excel as introduced XOR as a function which can take more than two arguments. Fine, but they seemed to have extended the definition in an non-useful way. I could see the function being useful if it returned True when one, and only one, of its arguments were TRUE and FALSE otherwise...

Hi Rick,

As I mentioned, I don't have access (unless at the local library) to anything later than 2010 as well. When I asked what the worksheet function XOR returned for:
= XOR(10,8)
I was trying to figure out what the function did with numbers (rather than Boolean values or Boolean results). As chicagocomputerclasses notes, the worksheet function treats any non-zero number as TRUE, so that answered my question as to bitwise comparison (it doesn't).

Anyways, I saw your response and think that maybe stepping thru the vba function Xor would demonstrate better? In short, as I understand it (and as you mentioned), Xor (or XOR) returns TRUE if one argument evaluates to TRUE and the other to FALSE. But... Xor conceptually, or by definition has always (AFAIK) worked on more than 2 arguments, and if the TRUE's (or binary 1's) are odd, then the return is TRUE (or the column in binary ends up with a 1). By example:

Option Explicit
  
'  For convenience
'  0000 0001  = 1
'  0000 0010  = 2
'  0000 0011  = 3
'  0000 0100  = 4
'  0000 0101  = 5
'  0000 0110  = 6
'  0000 0111  = 7
'  0000 1000  = 8
'  0000 1001  = 9
'  0000 1010  = 10
'  0000 1011  = 11
  
Sub StepThru()
  
  'Unable to access Excel2013 or later, but I think this replicates the worksheet function XOR
  Debug.Print "False Xor True = " & (False Xor True)
  Debug.Print "False Xor True Xor True = " & (False Xor True Xor True)
  Debug.Print "False Xor True Xor True Xor True = " & (False Xor True Xor True Xor True)
  
  'I believe this does as well
  Debug.Print "=XOR(3>12,4>6) returns " & UCase$(((3 > 12) Xor (4 > 6)))
  '(as noted, the above example in the doccumentation appears incorrect"
  
  'If supplying numbers to the worksheet function, in treating any non-zero number as TRUE, the worksheet function would equate to:
  Debug.Print "=XOR(10,8) returns " & UCase$((CBool(10) Xor CBool(8)))
    
  Debug.Print vbCr & "It seems clearer to me at least, to see in bit-wise operation"
  'whereas VBA's Xor does bitwise comparison if given numbers rather than booleans
  Debug.Print "10 Xor 8 = " & (10 Xor 8) '(returning 2)
  
  'Which is:
  ' 0000 1010 (10)
  ' 0000 1000 (8)
  '----------
  ' 0000 0010 (2)
  
  ' or "extending"
  Debug.Print "10 Xor 8 Xor 1 = " & (10 Xor 8 Xor 1) '(returning 3)
  
  'which is:
  ' 0000 1010 (10)
  ' 0000 1000 (8)
  ' 0000 0001 (1)
  '--------------
  ' 0000 0011 (3)
  
  '...or extended further
  Debug.Print "10 Xor 8 Xor 1 Xor 8 = " & (10 Xor 8 Xor 1 Xor 8)
  
  'which is:
  ' 0000 1010 (10)
  ' 0000 1000 (8)
  ' 0000 0001 (1)
  ' 0000 1000 (8)
  '--------------
  ' 0000 1011 (11)
  
End Sub

Hope that's a positive addition

Mark

 

[Rick Rothstein]

Hi Rick,

As I mentioned, I don't have access (unless at the local library) to anything later than 2010 as well. When I asked what the worksheet function XOR returned for:
= XOR(10,8)
I was trying to figure out what the function did with numbers (rather than Boolean values or Boolean results). As chicagocomputerclasses notes, the worksheet function treats any non-zero number as TRUE, so that answered my question as to bitwise comparison (it doesn't).

I understood that.

Anyways, I saw your response and think that maybe stepping thru the vba function Xor would demonstrate better? In short, as I understand it (and as you mentioned), Xor (or XOR) returns TRUE if one argument evaluates to TRUE and the other to FALSE. But... Xor conceptually, or by definition has always (AFAIK) worked on more than 2 arguments, and if the TRUE's (or binary 1's) are odd, then the return is TRUE (or the column in binary ends up with a 1).

I am going to have to have to respectfully disagree with you on that. Xor, as an operator, compares two and only two operands. When you write something like this...

False Xor True Xor True

that is two Xor logical operators chained together (which, having the same precedence, will be evaluated from left to right)... you can always chain logical operators together, but that is not the same as saying Xor (a single Xor that is) can process more than two operands. What Excel 2016 (apparently) has done is change the concept of XOR so that it can process more than two operands (something a function can be made to do but which a logical operator cannot). When it did this, the programmers had to create the logic the function would follow... I am disagreeing with the decision they made... to me, the more logical treatment would be implementing a "one, but none of the others" definition.

 

[GTO]

@Rick Rothstein:

I am going to have to have to respectfully disagree with you on that. Xor, as an operator, compares two and only two operands.

But... Xor conceptually, or by definition has always (AFAIK) worked on more than 2 arguments, and if the TRUE's (or binary 1's) are odd, then the return is TRUE (or the column in binary ends up with a 1).

Okay, that was less-than-clear verbiage on my part.

When you write something like this...

False Xor True Xor True

that is two Xor logical operators chained together (which, having the same precedence, will be evaluated from left to right)... you can always chain logical operators together, but that is not the same as saying Xor (a single Xor that is) can process more than two operands. What Excel 2016 (apparently) has done is change the concept of XOR so that it can process more than two operands (something a function can be made to do but which a logical operator cannot). When it did this, the programmers had to create the logic the function would follow... I am disagreeing with the decision they made... to me, the more logical treatment would be implementing a "one, but none of the others" definition.

With respect and affection, let me give this another shot. What I was trying to relay is I believe that in essence: The worksheet function XOR simply is chaining together however many Xor's are needed, or to put it another way, sending the return of the first two arguments to be Xor('d) by the third argument, and so on.

FWIW, I would note the first few lines in This article

"Exclusive or or Exclusive disjunction is a logical operation that outputs true only when inputs differ (one is true, the other is false).^[1]
...
More generally, XOR is true only when an odd number of inputs are true. A chain of XORs—a XOR b XOR c XOR d (and so on)—is true whenever an odd number of the inputs are true and is false whenever an even number of inputs are true."

Thus, and I could be wrong of course, but I do not see the worksheet function as having changed the concept of Exclusive Or. Rather, the worksheet function simply allows for a single return from multiple/dependent Xor tests. Would you agree with that or do you see an error in my conclusion?

I would also mention that I am of course not arguing against a function that would only return TRUE if only one value is different from the others.

Mark

 

[chicagocomputerclasses]

https://en.wikipedia.org/wiki/XOR_gate

[h=2]More than two inputs[/h]Strict reading of the definition of exclusive or, or observation of the IEC rectangular symbol, raises the question of correct behaviour with additional inputs. If a logic gate were to accept three or more inputs and produce a true output if exactly one of those inputs were true, then it would in effect be a one-hot detector (and indeed this is the case for only two inputs). However, it is rarely implemented this way in practice.
It is most common to regard subsequent inputs as being applied through a cascade of binary exclusive-or operations: the first two signals are fed into an XOR gate, then the output of that gate is fed into a second XOR gate together with the third signal, and so on for any remaining signals. The result is a circuit that outputs a 1 when the number of 1s at its inputs is odd, and a 0 when the number of incoming 1s is even. This makes it practically useful as a parity generator or a modulo-2 adder.

 

[chicagocomputerclasses]

Here is what I get in Python, which is the option 2, which is logically similar to Excel XOR implementation

In: 1 ^ 1 ^ 1
Out: 1


In: 1 ^ 1 ^ 0
Out: 0


In: 0 ^ 0 ^ 0
Out: 0


In: 1 ^ 0 ^ 0
Out: 1