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
If you prefer the reference of the cell, we have this
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
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
You can more examples and explanations with the IF function in this article.