XOR Function

Introduction

Serious programmers, not Excel programmers, that is, probably know the XOR function as it performs what is called exclusive OR. What that means is that if there are two logical statements to consider, XOR returns TRUE if either statement is TRUE, it returns FALSE if both statements are TRUE and if neither statement is TRUE, XOR also returns FALSE. Look at the following examples:

The Basic Rules of XOR with Two Logicals ...

Notice, in the screenshot above, TRUE and FALSE are equivalent to 1 and 0. That means we can use either.

More than Two Logicals

There is nothing wrong with the above examples but in real life, there may be more than two logicals: that is more than two TRUE and FALSE in any one example. Like the following:

Three Logicals and XOR ...

In this new example, there are three logicals: work, rest and play. From the above screenshot, we can deduce the multi logicals XOR rules:

• zero TRUE returns FALSE
• odd number of TRUE and XOR returns TRUE
• even number of TRUE and XOR returns FALSE

The following screenshots develop the three logicals example by including a SUM column, a Meaning Column and an IFS column, together with the formulas for the Meaning and IFS columns:

The SUM column helps to illustrate the 0, Odd and Even nature of a solution ...

The SUM column confirms the number of TRUE results there are for each row: 0, 1 , 2 and 3 in this example.

A Meaning or Natural Language Column ...

As we try to make our example clearly understandable and communicative, I have added the natural language Meaning column, column L, together with the formula I created for that, in column M.

Fully Worked Solution ...

I have included the IFS formula, columns N and O partly because it is very easy to do, it includes 0, ISODD() and ISEVEN() that help us to resolve any problems we might have with an example. We could create a Meaning column using the IFS function rather than the nested IF statement I did use for that. Feel free to do that yourself!

More than Three Logicals

There is nothing to stop us from including four, five, many more logicals in an example: the rules shown here, together with my tables and formulas, will work or can be extended to cope with the extra logicals at any time.

Conclusions

I have to confess, I have never used the XOR function that I remember but now that I have thought about it, I can see uses for it. The IFS function is a useful addition to this page too, as it follows hot on the heels of the Nested IF, IFS and CHOOSE page I wrote yesterday: https://excelmaster.co/nested-if-ifs-and-choose/

Download my file here:

xor_functionDownload

Duncan Williamson

6th December 2019