Function XOR in Excel

Function XOR in Excel
Last Updated on 09/11/2023
Reading time: 2 minutes

What is the purpose of the XOR function in logic?

In logic, there are two ways to specify an OR

  1. 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

  2. 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.

Leave a Reply

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

Function XOR in Excel

Reading time: 2 minutes
Last Updated on 09/11/2023

What is the purpose of the XOR function in logic?

In logic, there are two ways to specify an OR

  1. 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

  2. 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.

Leave a Reply

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