XOR Only Works Correctly for Two Values

March 15, 2022 - by Bill Jelen

Problem: Excel 2013 introduced an Exclusive OR (XOR) function. It won’t work correctly for more than two values.

The XOR function counts if the number of TRUE arguments is odd. — Figure 376. Flo ordered three items, but XOR incorrectly returns TRUE.

Strategy: Blame it on Electrical Engineers. They use XOR chips in circuits. Despite it’s name, the XOR chip counts if an odd number of inputs are TRUE. The Excel team decided to emulate the chip instead of English. To truly do what all non-EE’s understand to be XOR, you could use =COUNTIF(B7:I7,True)=1.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tim Johnson on Unsplash