BITXOR Function

Basic Description

The Excel BitXor function returns the bitwise ‘XOR’ (exclusive ‘OR’) for two supplied integers. Note: the BitXor function was only introduced in Excel 2013 and so is not available in earlier versions of Excel.

Syntax: BITXOR( number1, number2 )

where the supplied number arguments are positive integers.

BitXor Function Example 1
 Formula:
A B
1 =BITXOR( 5, 6 )  (5 and 6 have binary forms 101 and 110)
2
 Result:
A B
1 3  (decimal form of 011)
2

In the above example:

Decimal-Binary Conversion

If you want to work through the stages of a bitwise ‘XOR’ operation you can use the Excel DEC2BIN and BIN2DEC functions to convert between decimal and binary forms.

  • The binary form of 5 is 101.
  • The binary form of 6 is 110.
  • One (and only one) of the binary numbers 101 and 110 has the digit ‘1’ at position 1 (counting from the right). Therefore, within the result, the rightmost digit is a 1.
  • One (and only one) of the binary numbers 101 and 110 has the digit ‘1’ at position 2 (counting from the right). Therefore, within the result, the digit second from the right is a 1.
  • Both of the binary numbers 101 and 110 have the digit ‘1’ at position 3 (counting from the right). Therefore, within the result, the third digit from the right is a 0.
  • The bitwise ‘XOR’ result is therefore the binary number 011.
  • This number is returned in decimal form, as the number 3.

 

BitXor Function Example 2
 Formula:
A B
1 =BITXOR( 9, 12 )  (9 and 12 have binary forms 1001 and 1100)
2
 Result:
A B
1 5  (decimal form of 0101)
2

In the above example:

  • The binary form of 9 is 1001.
  • The binary form of 12 is 1100.
  • One (and only one) of the binary numbers 101 and 110 has the digit ‘1’ at position 1 (counting from the right). Therefore the rightmost digit of the result is a 1.
  • Neither of the binary numbers 101 and 110 have the digit ‘1’ at position 2 (counting from the right). Therefore, within the result, the digit second from the right is a 0.
  • One (and only one) of the binary numbers 101 and 110 have the digit ‘1’ at position 3 (counting from the right). Therefore, within the result, the third digit from the right is a 1.
  • Bothc of the binary numbers 101 and 110 have the digit ‘1’ at position 4 (counting from the right). Therefore, within the result, the fourth digit from the right is a 0.
  • The bitwise ‘XOR’ result is therefore the binary number 0101.
  • This number is returned in decimal form, as the number 5.

 

BitXor Function Errors

#NUM!

Occurs if either:

    – one or both of the supplied number arguments is a non-integer
or
    – one or both of the supplied number arguments is negative or is greater than (2^48)-1.
#VALUE! one or both of the supplied number arguments is non-numeric.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment