Function XOR

Function XOR is a new logical function that corresponds to the exclusive OR

What is the XOR function?

In logic, there are two ways to specify an OR context

• The inclusive OR. One or more of its parameters are true and the result of the test is TRUE, it is the function OR
• The exclusive OR. One and only one parameter is true and the result of the test is TRUE, it is the function XOR

The XOR function is present in Excel since Excel 2013

When use the XOR function

The best is to present this function in a comprehensive example.

We want to buy a new computer but (of course) our budget is small. So we will consider 2 elements of the computer: the size of the hard-drive and the RAM. We select the computer if

• The hard-drive has at least 500Mo
• The RAM is greater than 6Go.

If a computer has one of this feature, we select it but if it has both, we refuse it (too expensive)

Test with the OR function

Let's make a test with the function OR

If you work with a Table, the formula is

=OR([@[Hard-Drive]]>=500,[@RAM]>=6)

If you prefer the reference of the cell, we have this

=OR(B2>=500,C2>=6)

As you can see, with a function OR, most of the computers can be selected. This is because with OR, if one or more tests is TRUE, the function return TRUE.

For instance, the computer 3 has a hard-drive greater than 500 and also the RAM greater than 6. The 2 tests are TRUE, so the function OR returns TRUE.

Test with the XOR function

Now, if we change the formula with the function XOR, the result is different

=XOR([@[Hard-Drive]]>=500,[@RAM]>=6)

Now, only 2 computers match the test.

• The computer 1 has a hard-drive of 512 (test TRUE) and RAM of 4 (test FALSE).
• Only one test is TRUE so the OR function returns TRUE
• The computer 3 has a hard-drive of 512 (test TRUE) and RAM of 8 (test TRUE)
• Both tests are TRUE, so the XOR function return FALSE

Integer the test in a IF function

To avoid to leave the result TRUE or FALSE in your cells, you can integer the test in a IF function like this

=IF([@[Result XOR]],"Select","")

You can more examples and explanations with the IF function in this article.

Related articles

Have a look at these other articles that could help you in your work