Excel: Creating an Exclusive Or (XOR) Function

SUMMARY

There is no built-in function in Microsoft Excel that performs the XOR (Exclusive Or) operation. It can be reproduced by a combination of mathematical and logical functions.

MORE INFORMATION

The XOR function is a Boolean operation that is normally used to operate on two variables. In this case, it evaluates to TRUE when one of the variables equals FALSE and the other TRUE. If both variables have the same value, XOR evaluates to FALSE.

If there are more than two variables, XOR will evaluate to TRUE if an odd number of the variables involved equals TRUE.

To reproduce the XOR operation in Excel, use the following formula:

  =IF(MOD(test1+test2+...+testn,2),TRUE)

How the Formula Works

1. The formula sums all the variables that return TRUE. (In Excel, TRUE has the numerical value of one, FALSE equals zero.)

2. The MOD statement divides this sum by two and returns the remainder, which will be one if an odd number of the tests are TRUE, and zero if an even number of the tests are TRUE.

3. If the remainder is one, the IF statement evaluates to TRUE, and the function returns TRUE. If the remainder is zero, the IF statement evaluates to FALSE, and the function returns FALSE by default.

Example

An insurance company has two criteria for evaluating a driver's risk level: the driver's age and whether or not he has a previous driving offense. A driver who is either under age 20, or has a previous offense is considered medium risk. (Assume that a person who is neither is low-risk, one who is both is high-risk). The Exclusive Or (XOR) can be used to identify all the drivers who are in the medium risk category.

Enter the following data into a worksheet:

   A1:              B1: Age           C1: Previous Offenses
   A2: Tom          B2:  25           C2:  0
   A3: Dick         B3:  40           C3:  4
   A4: Harry        B4:  19           C4:  0
   A5: Bill         B5:  17           C5:  1

   =IF(MOD((B2<20)+(C2>0),2),TRUE)

In this example, to sum all the instances of people who are in the medium risk category, use the following formula:

  {=SUM(MOD((B2:B5<20)+(C2:C5>0),2))}

REFERENCES

"Microsoft Excel Function Reference," version 3.0, page 155

"Microsoft Excel Functions and Macros," version 2.x, pages 87-88