Excel XOR

Excel 2010 doesn’t have an XOR function. The newer versions do. I have needed this form of logic a couple of times recently and I found a good tip here.

XOR works like this.

A B Result
1 1 0
1 0 1
0 1 1
0 0 0

So, if you don’t have an XOR function you can write a long version of the XOR as follows

=OR(AND(NOT(A),B),AND(A,NOT(B)))

This is how the XOR operation is defined if you study boolean logic. I did 25 years ago.

But there is a clever way to do it in Excel.

The web page lined above noted that the equivalent can be done from the observation that XOR is nothing but <> (the not equal to sign).

So, instead of going crazy with brackets writing the lengthy formula above. you can just use

=A<>B

One aside that I found out. You need to be careful with your brackets. I found out that

This

 =IF(B4=""<>I4<>"","",1)

Is not the same as this

=IF((E15="")<>(L15<>""),"",1)

Hint: Use the second form with the extra brackets.

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.