Microsoft KB Archive/79235

{| = Excel: Creating an Exclusive Or (XOR) Function =
 * width="100%"|

Last reviewed: November 30, 1994

Article ID: Q79235

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 In cell D2, enter the formula: =IF(MOD((B2<20)+(C2>0),2),TRUE) Highlight cells D2 to D5 and choose Fill Down from the Edit menu. Cells D3 and D4 will contain the value TRUE, D2 and D5 will contain FALSE. 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))} Do not enter the curly brackets manually. Enter the formula as an array formula by pressing CTRL+SHIFT+ENTER simultaneously and the brackets are automatically entered.