# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Can this be right?  Excel has no XOR function???

## LSB M

Find it hard to believe that Excel doesn't have the exlusive OR funcion.

I need something to say either A or B but NOT both.  I suppose can write a longer formula to achieve this but....  :Confused:

----------


## davesexcel

=IF(A1="No","GoodBye",IF(A1="Yes","Hello",""))

you mean something like this??

----------


## David Biddulph

"davesexcel" <davesexcel.2bht4q_1153816807.0158@excelforum-nospam.com> wrote
in message news:davesexcel.2bht4q_1153816807.0158@excelforum-nospam.com...
>
> =IF(A1="No","GoodBye",IF(A1="Yes","Hello",""))
>
> you mean something like this??

No.  The exclusive OR function is
=AND(OR(A1,B1),NOT(AND(A1,B1)))
--
David Biddulph

----------


## Bernard Liengme

No explicit function but you can always make up one
=IF(COUNTIF(A1:B1,"X")=1,TRUE,FALSE)
returns TRUE if and only if there is one X in the range.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LSB M" <LSB.M.2bhpfc_1153812008.1361@excelforum-nospam.com> wrote in
message news:LSB.M.2bhpfc_1153812008.1361@excelforum-nospam.com...
>
> Find it hard to believe that Excel doesn't have the exlusive OR
> funcion.
>
> I need something to say either A or B but NOT both.  I suppose can
> write a longer formula to achieve this but....  :Confused: 
>
>
> --
> LSB M
> ------------------------------------------------------------------------
> LSB M's Profile:
> http://www.excelforum.com/member.php...o&userid=36258
> View this thread: http://www.excelforum.com/showthread...hreadid=564591
>

----------


## excelblogger

> Find it hard to believe that Excel doesn't have the exlusive OR funcion.
> 
> I need something to say either A or B but NOT both.  I suppose can write a longer formula to achieve this but....



Finally we can soon answer yes to this; there is (soon) an XOR-function

In Excel 2013 there is a new logical function XOR that can be used for testing exclusive disjunctions or exclusive or, on two or more logical tests. That is testing A or B but not both" and similar...

Instead of writing this in Excel 2010 and previous versions:

 =OR(AND(A1;NOT(A2);NOT(A3);NOT(A4));AND(A2;NOT(A1);NOT(A3);NOT(A4));AND(A3;NOT(A1);NOT(A2);NOT(A4));AND(A4;NOT(A1);NOT(A2);NOT(A3)))

You can now write this in Excel 2013:

=XOR(A1;A2;A3;A4)

I have written a blog post on this new function, and also provided a spreadsheet example on how you can use it:

http://excelblogger.com/xor-excel-2013-function/

Best regards from anders

.............,,,,,,,,,,,,,,,,,
http://excelblogger.com/
Get more from your data and Excel

----------


## shg

> Instead of writing this in Excel 2010 and previous versions:
> 
> =OR(AND(A1;NOT(A2);NOT(A3);NOT(A4));AND(A2;NOT(A1);NOT(A3);NOT(A4));AND(A3;NOT(A1);NOT(A2);NOT(A4)); AND(A4;NOT(A1);NOT(A2);NOT(A3)))
> 
> You can now write this in Excel 2013:
> 
> =XOR(A1;A2;A3;A4)



If A1:A4 were logical values, you might instead have written 

=MOD(A1+A2+A3+A4, 2) =1

or 

=MOD(SUM(--A1:A4), 2) =1

----------


## excelblogger

That’s absolutely a more efficient alternative.
So its
=MOD(A1+A2+A3+A4, 2) =1
Vs.
=XOR(A1;A2;A3;A4)

The advantage of XOR is maybe more that it’s easier to understand…

----------


## PatrickOfLondon

If you need an XOR function that will return "true" if ONLY ONE of several arguments is true, try entering this as a cell formula:

=1=SUMPRODUCT(B10:F10*1)

The cells in the range (in this example, B10:F10) must contain (or equate to) either TRUE or FALSE (or be blank). If ONLY ONE of the cells is TRUE the above formula will return TRUE; otherwise, FALSE.

If you want to do something similar if a fixed count -- e.g. 2, 3, 4... -- of the cells contain TRUE, just change the first parameter, e.g.
=2=SUMPRODUCT(B10:F10*1)
=3=SUMPRODUCT(B10:F10*1)
=4=SUMPRODUCT(B10:F10*1)

----------


## zweiblumen

> If A1:A4 were logical values, you might instead have written 
> 
> =MOD(A1+A2+A3+A4, 2) =1
> 
> or 
> 
> =MOD(SUM(--A1:A4), 2) =1



Thank you for this post, which has helped me with an XOR case that I was puzzling over. There is a small error in the formula as stated. The divisor should be 4 in your example, as you are evaluating 4 values. With a divisor of 2, if there are 3 TRUE statements, this formula returns TRUE when it should be FALSE.

=MOD(A1+A2+A3+A4, 4) =1

----------

