Problem: I need to make sure every attendee to the company dinner chose exactly one entree. The new Exclusive OR (XOR) function introduced in Excel 2013 should make this easy, right?
- Flo ordered three items, but XOR incorrectly returns TRUE.
Strategy: It is a horrible story, but the short answer is No.
There is a brilliant team in Redmond who decides what goes into the next Excel. They add functions that are well thought out.
Somewhere else, there is a committee called the Open Document Spreadsheet standards board. These people added 50 functions to the ODS standard. When I look through the list, many of the things were already possible with other Excel functions. However, Microsoft seemed to feel compelled to add those 50 functions to Excel 2013 so they could continue to open ODS documents. XOR is one of those functions.
Like you, I was sort of jazzed that Excel 2013 offered XOR, but as I started to use it, it did not work like I thought that it should. I even filed a bug with Microsoft that XOR(True,True,True) was returning True.
After all, XOR should mean Exclusive Or, which means they only chose one item. Not three items. Not five items. One item.
But, this XOR function is programmed to operate just like the 7486 Chip Set. And that chip set looks at the first pair of values, decides if they are XOR, and then takes that result on to compare with the next value. The chip would evaluate Flo's dinner choices like this:
- Steak=False, Chicken = True. Only 1 is selected, so XOR is True
- Result from Steak|Chicken is True, Fish is True, so XOR is False
- Result from Steak | Chicken | Fish is False, Veggie is True, so XOR is True. Argh.
I quickly got into a argument that based on the English language meaning of "Exclusive Or", their logic was wrong. The Electrical Engineers in the argument quickly explained that there are millions of 7486 chip sets in use and therefore, their need to have XOR match the chip results beats out our need to make sure Flo didn't order three meals. They've come to accept that XOR does not stand for eXclusive OR, but actually stands for "a compleX function to count if an Odd number of inputs are tRue".
So, although the function says "XOR", it really is a bizarre function to measure if there are an odd number of True values. I have no idea when you will need that.
To truly do what all non-EE's understand to be XOR, you could use =COUNTIF(B7:I7,True)=1. Heck, this is even shorter and easier to type than specifying each input cell in XOR. This provides more evidence that the 50 new functions from the ODS board weren't all necessary or well thought-out.