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.
Leave a Reply