Excel: Creating an Exclusive Or (XOR) Function

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.

REFERENCES

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

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


KBCategory: kbother
KBSubcategory:

Additional reference words: 2.10 2.10c 2.10d 3.00 2.20 2.21 2.1 2.1c 2.1d
2.2


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.