Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet XOR (97 SR2)

    Does anyone know how to enter XOR into a cell formula? I know I can use VBA but don't want to have to. Or, does anyone know how to build XOR from scratch (AND, NOT, OR)?

    The funny things we try...
    thanks in advance

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet XOR (97 SR2)

    I don't see a worksheet XOR function. You could create a User Defined Function like this:

    <pre>Public Function bXOR(bV1 As Boolean, bV2 As Boolean) As Boolean
    bXOR = bV1 Xor bV2
    End Function
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet XOR (97 SR2)

    try the formula
    shown below (I assume that the values to be tested are in A2, B2)

    =AND(OR(A2,B2),NOT(AND(AND(A2,B2),OR(A2,B2))))

    it will give and XOR as shown by the following.

    1 0 TRUE
    1 1 FALSE
    0 1 TRUE
    0 0 FALSE

    Then wait a little while, I am sure that there is someone else on the lounge who can give a shorter method to achieve XOR using AND, OR and NOT.

    HIH

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Worksheet XOR (97 SR2)

    I think you could simplify this to...
    <pre>=AND(OR(A2,B2),NOT(A2=B2))
    </pre>

    Translated into English as A2 or B2 but not when they are both the same!

    StuartR

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Worksheet XOR (97 SR2)

    Marty,

    You can use logical arithmetic for this. Simply put, true=1 and false=0. Suppose you have cell a1 and b1 with values of true or false. You can write a simple formula like:
    =(a1+b1)=1.
    Suppose a1=true (which has arithmetic value=1) and b1=false (arithmetic value=0). The resulting sum will be true (=1) only if exactly one of a1 or b1 is true (ie, XOR).

    If a1 and b1 have values in them that you want to test and determine if exactly one test is met, then the formula becomes something like
    =((a1=value1)+(b1=value2)=1)

    You can create the other logical conditions in similar ways:
    OR: a1+b1>0
    AND: a1*b1=1

    See attached workbook for some examples.

    HTH

    Fred
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet XOR (97 SR2)

    Thanks. I got that to do bitwise XORs by changing the data type. Now WXOR(10,8) = 2 as it should.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •