What is the XOR function?

Function XOR is a logical function integrated into Excel.

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

• The inclusive OR: If one or more of its parameters are true, the result of the test is TRUE. In Excel this is OR function.
• The exclusive OR: If one and only one parameter is true, the result of the test is TRUE. This it is the XOR function.

The XOR function is present in Excel since Excel 2013.

When to use the XOR function

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

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

In our example below we are looking for a computer with one of these elements:

• At least a 500MB Hard Drive
• At least 6GB of RAM

If a computer has one of these elements, we consider it but if it has both, we don't reject it (as it would be out of our budget)

Test with the OR function

Let's make a test with the OR function.

If you use a Table, the formula would be:

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

If you prefer to use cell references, the formula would be:

=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

Integrate the test in an IF function

To avoid leaving the result TRUE or FALSE in your cells, you can integrate the test in a IF function like this:

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

You can find more examples and explanations of how to use the IF function in this article.