MacLochlainns Weblog

Since Excel 2007 and 2008 don’t support a direct exclusive or – =XOR() – function, you’d think the logical operators could do it. Something like this:

=AND(NOT(AND(A1,B1)),OR(A1,B1))

It surprised me when it didn’t work. Curious, I figured out why it wouldn’t work. The AND() function doesn’t work as qualified in the help file.

The AND() function only works correctly when both cell values are not null values. It returns a Boolean true when one value is true and the other value is null, which was quite a surprise.

Fortunately, the IF() function works correctly. If you check a cell with a null value, the IF() function finds it false. That means you can wrap the cells in the IF() function, like:

=AND(NOT(AND(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE()))),OR(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE())))

Naturally, you can substitute the respective string literal for either the TRUE() or FALSE() function calls. You could also rewrite the formula to check for a null value by using two double quotes. By the way, it’s fun to note that the TYPE() function recognizes a null value cell as a number data type.

It’s ashame it doesn’t work the way you’d think it should.