Looking for BOOLEAN ALGEBRA functions (specifically XOR)

[RL Jones]

I am looking for BOOLEAN Algebra Functions. Can't find any help in EXCEL.
While I can work around the OR, AND, and NOT, I need XOR.

 

[Mike H]

Hi,

Excel doesn't have it but you can write your own quite simply

=IF(COUNTIF(A1:B1,"Some Value")=1,TRUE,FALSE)

Mike

 

[Rick Rothstein]

I'm pretty sure this is it...

=OR(AND(A1,NOT(B1)),AND(NOT(A1),B1))

 

[Mike H]

Rick,

Can be abbreviated to:-

=(A1<>0)+(B1<>0)=1

and it also works if both cells are blank.

Mike

 

[Rick Rothstein]

Ah, yes... very good. And I guess that could be written this way as well...

=NOT(A1)+NOT(B1)=1

 

[Mike H]

Yes it could and until Excel include

=XOR(A1,B1) then they are as short as it gets.

Mike

 

[Harlan Grove]

Ah, yes... very good. And I guess that could be written this way as well...

=NOT(A1)+NOT(B1)=1

....

If A1 and B1 would always be 1s or 0s, even shorter:

=MOD(A1+B1,2)

 

[Rick Rothstein]

Ah, yes... very good. And I guess that could be written this way as

...

If A1 and B1 would always be 1s or 0s, even shorter:

=MOD(A1+B1,2)

Provided the user didn't need to see the result of the operation as
TRUE/FALSE, of course.

 

[Herbert Seidenberg]

An XOR could operate on any number of binary bits.
An odd parity checker would XOR all given bits,
thus checking for an odd number of bits in a word:
=MOD(1+MOD(HEX2BIN("AE")-1,9),2)
I vote for Harlan's formulas, having lifted them from
his post dated Jun 16, 2003.

 

[Dana DeLouis]

... until Excel include =XOR(A1,B1)

It's included as an Excel vba function.
I have no idea why it's not included as a Worksheet function.

b = a Xor b

= = = =
Dana DeLouis

 

[Rick Rothstein]

I wasn't saying to not use Harlan's formula... just noting his is returning
a number whereas the other posted formula returned TRUE/FALSE. If you used
in an expression, that difference wouldn't matter; but since all submissions
were shown as stand-alone formulas, I just figured I would note the
difference in what each returned. Besides, I wanted to give an opening to
Harlan to post back...

=MOD(A1+B1,2)=1

I think he likes being able to do that, so I thought I would be nice to him
and leave the door open for him to do that.<g> Of course, now that I took
that opportunity away from him in my response to you, I'll have to give him
a different opening for a response to this thread. Let me see... I know... I
do wonder which is the more efficient construction... Harlan's formula which
uses a MOD function call, mine which uses two NOT function calls or Mike's
which uses two logical comparisons.

 

[cwebster05]

I am looking for BOOLEAN Algebra Functions. Can't find any help in EXCEL.
While I can work around the OR, AND, and NOT, I need XOR.

I am using Excel 2007 so I don't know if this applies to earlier versions but here is an easy way.

=ISODD(A4+B4)
If the contents of A4 and B4 are digits 1 or 0, This returns TRUE or FALSE depending on whether the sum is odd or even (same as XOR function)

=N(ISODD(A4+B4))
If you want it to return a 1 or 0, just use the "N" function (converts TRUE or FALSE to 1 or 0)

This also works for more inputs like this:
=N(ISODD(A4+B4+C4)
You can have as many inputs as you want.

cwebster

 

[jvhilbs]

Since it would be nice to have the formula in a cell & not just in VBA, it can be created in an Excel Add-in.

Open a new Excel Workbook & save it as Add-in file type (extension .xlam)
Then open the VBA editor (Alt + F11)
Right click on the project & Insert Module
Then copy & in paste this function:

'The "_" is necessary to create unique named formula as Xor is already taken
Function XOR_(Range1 As Range, Range2 As Range) As Boolean
XOR_ = Range1 Xor Range2
End Function

Save & then you'll have to go back to excel & select your new add-in file (easier to google than for me to explain, but it's not difficult).

Now Excel won't auto complete the formula name, but XOR_(A1,B1) will now be available!