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....

[davesexcel]

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

you mean something like this??

[David Biddulph]

"davesexcel" <[email protected]> wrote
in message news:[email protected]...
>
> =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" <[email protected]> wrote in
message news:[email protected]...
>
> 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....
>
>
> --
> 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