XOR logic in Excel

Unlike the OR function which returns TRUE if only 1 condition is TRUE, the XOR function returns TRUE if the number of TRUE conditions is ODD and FALSE if the number of TRUE Conditions is EVEN.

Like the OR Function, at least one of the test conditions must evaluate to TRUE to return TRUE. XOR function will return FALSE if all conditions evaluate to FALSE

=OR( 2=2, 2>3, 4>3 )=TRUE this is because at least 1 condition is TRUE

=XOR( 2=2, 2>3, 4>3 )=FALSE this is because the number of TRUE Conditions is EVEN

If no logical conditions are found, XOR returns #VALUE error

Interestingly, the XOR function can be represented by the minus (-) Sign

(2=2) – (2>3) – (4>3) => FALSE

(2>2) – (2>3) – (4>3) => TRUE

How is this possible?

In Excel, the numeric equivalent of a FALSE Boolean value is Zero (0) while any other number (positive or negative) is equivalent to a TRUE Boolean Value.

(2=2) – (2>3) – (4>3) => TRUE – FALSE – TRUE ==> 1- 0 -1 ===> 0 ( FALSE )

(2>2) – (2>3) – (4>3) => FALSE – FALSE – TRUE ==> 0- 0 -1 ===> -1 (TRUE )

NB: One major difference between the minus (-) symbol and the XOR function is that the XOR function is not able to Spill logically!

FILTER Function Using XOR logic

Since the  FILTER function has a spill capability, we shall use it to show the difference between the minus (- ) sign and the XOR function

=FILTER(Supplier[Name],
XOR(ISNUMBER(Supplier[1st pay]),ISNUMBER(Supplier[2nd pay]))
)
….Since XOR does not spill then the function result to CALC# error

=FILTER(Supplier[Name],
ISNUMBER(Supplier[1st pay])-ISNUMBER(Supplier[2nd pay]))
…this works since the minus sign is able to spill

IF Function Using XOR logic

If you are checking single records instead of arrays, both XOR and Minus (-) sign works the same

NOTE:

XOR function can be used to test for Empty and non-empty cells

=IF(XOR(Supplier3[@[1st pay]:[2nd pay]]), TRUE, FALSE)

Above function will return:

  1. #VALUE error if all cells are empty
  2. TRUE if the number of non-empty cells are ODD
  3. FALSE if the number of non-empty cells are EVEN

COUNTIF Function Using XOR logic

COUNTIF function does not allow array calculations but there is a walk-around

Count all the suppliers paid only 1 time

=SUM(
     ABS(
          ISNUMBER(pays[1st pay])-ISNUMBER(pays[2nd pay] ) ) )

SUMIF Function Using XOR logic

Just like the COUNTIF function, SUMIF does not allow array calculations but there is a walk-around

=SUM(
     ABS(ISNUMBER(tblpay[1st pay])-ISNUMBER(tblpay[2nd pay])) * tblpay[Sales] )

DOWNLOAD WORKSHEET

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.