Exclusive Or formulas with the XOR function

Most frequent Excel users will be familiar with both the AND and OR functions, but here’s a quick recap just in case.

 

Both AND and OR are Boolean functions, which means they will return either TRUE or FALSE based on the set of conditions you pass them.

 

The below table shows the top 10 most successful football clubs in England, based on major trophies won.

 

ExclusiveOrFunction1

If you wanted to use the AND function to pick up any times who have won both the League and Europe you could use:

=AND(D3>0,G3>0)

 

Drag the formula down and anyone who has will return TRUE. Or if you wanted to know if the team has won either the League Cup or Europe, you could use:

=OR(F3>0,G3>0)

 

These are very basic examples, and of course these formulas can be used with much more complex criteria and with more than two arguments.

 

Excel has been crying out for a variation of the OR function that tells you whether just one of the inputs is true, as OR only tells you that at least one is true. Perhaps you need to ensure a user account only belongs to one group from a list, the OR function doesn’t do the trick. But the new XOR (standing for exclusive or) does. The bad news is it is only available from Excel 2013 onwards.

 

Sticking with a basic example for now, to return TRUE for any teams that have won one of the domestic trophies more than 10 times you would copy down the below formula. Liverpool would return TRUE to the exclusive or, because they’ve only won one of the trophies over 10 times whereas Manchester United and Arsenal would return FALSE because have won two of the trophies over 10 times.

=XOR(D3>10,E3>10,F3>10)

Leave a Reply

Your email address will not be published. Required fields are marked *