What is the purpose of the XOR function in logic?
In logic, there are two ways to specify an OR
- Inclusive OR
This is when any argument of the function returns TRUE. You can have only one or more than one TRUE, the function will return TRUE.
This is the OR function in Excel - Exclusive OR
In this situation, only one argument can return TRUE, but not both. This is the XOR function.
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 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, 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.
- 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
- Computer 3 has a hard drive of 512 (test TRUE) and RAM of 8 (test TRUE)
- Both tests are TRUE, so the XOR function returns 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 an 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.
