EXCEL FUNCTIONS-XOR

INTRODUCTION

Life is full of logical conditions. We wake up and we have two choices. Get out of the bed or again go to sleep.

Same is the case with our applications or programs in EXCEL which we create. Every decision is full of conditions which need to to fulfilled before the final execution.

But there are different types of conditions. Some are necessary and some are optional. So this article will be focusing on the conditions which are necessary i.e. have to be met at any cost for the execution of the program.

We are discussing about the XOR FUNCTION in EXCEL.

XOR FUNCTION RETURNS TRUE IF THE GIVEN CONDITIONS ARE OPPOSING TO EACH OTHER WHICH MEANS IF ONE CONDITION IS TRUE THEN OTHER MUST BE FALSE. XOR FUNCTION RETURNS A FALSE IF ALL THE CONDITIONS ARE SAME.

XOR function comes under the LOGICAL FUNCTIONS category in Excel.

It simply behaves like the binary XOR. [A separate section covers the information at the end of the article]

It checks if both the conditions are mismatch or not. [ It’ll return a false for both same conditions. Mismatch will result in True]

 *The generalized statements mentioned above is only meant for TWO VARIABLES for the simplification purpose. Three variables will be discussed separately.

This article will discuss the purpose , syntax, use and examples of AND FUNCTION in EXCEL.

PURPOSE OF XOR IN EXCEL

XOR FUNCTION RETURNS TRUE IF THE CONDITIONS ARE MISMATCHED AND FALSE IF THE CONDITIONS ARE MATCHED. [TWO VARIABLES ONLY]
XOR HAS THE BINARY LOGIC AS
AB′+A′B
WHERE ′ MEANS BAR.[ Switch the value from True to False or False to true ]

PREREQUISITES TO LEARN XOR

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  • Some information about the BINARY MATHEMATICS is an advantage for the use of such formulas. 
  • Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?

SYNTAX: XOR FUNCTION

The Syntax for the function is

=XOR(CONDITION 1, CONDITION 2, CONDITION 3, …… SO ON)

CONDITION1  Any condition (e.g. if any cell>12)

CONDITION2  Any condition (if any cell <45)

Atleast TWO CONDITIONS are needed. At the most, any number of conditions can be used. [UPTO 254]

EXAMPLE:XOR FUNCTION IN EXCEL

DATA SAMPLE

Let us take a few examples.We have two numbers and we want to find the pairs which have a difference of more than 30.

NUMBER 1NUMBER 2
  
4637
3261
4062
9494
3255
7497
4090
6874
559
3036
1529
7368
8040
5518
XOR FUNCTION EXAMPLES IN EXCEL

STEPS TO USE XOR FUNCTION

  • Select the cell where we want the result. For our example, we selected the cell H7.
  • Put the expression for the  given condition as =XOR((F7-G7)>30,(G7-F7)>30).
  • Drag down the formula through the column.
  • The result will appear correctly as shown in the picture above.
  • The individual formula used is also shown.

EXPLANATION

We have taken the pair of 14 numbers in the sample above.

The condition for which the expression should be true is THE DIFFERENCE BETWEEN THE TWO NUMBERS SHOULD BE MORE THAN 30.

So for the first examples, we have put the function as =XOR((F7-G7)>30,(G7-F7)>30)

The function gives true when either of the condition is true. We have two conditions where one number is greater than the other by 30 or where the other number is greater than the first by 30.

So we put the function and the XOR FUNCTION suits perfectly and gives correct results.

KNOWLEDGE BYTES

BINARY XOR

BINARY MATHEMATICS have just two number-0 and 1.

Its just like the DECIMAL SYSTEM which we use in day to day life which has ten numbers 0 to 9.

So XOR is Exclusive OR .

The symbol for XOR is shown by a PLUS INSIDE A CIRCLE. ⊕The expression for XOR is AB′+AB′ If we have 0 and 1, the results will be as shown below.

0⊕0=0

1⊕0=1

0⊕1=1

1⊕1=0

where 1 is TRUE and 0  is false.

So we can see that we get TRUE only when the states are alternate i.e. both the conditions are opposing .

BINARY XOR WITH THREE VARIABLES

As we know that just like AND FUNCTION, OR FUNCTION  , XOR FUNCTION can have many inputs. We discussed the two inputs in details. Let us have a look at the three inputs.

The expression for the THREE INPUTS XOR WILL BE

ABC+A′B′C+A′BC′+AB′C′

The truth table for three input XOR is

A B C RESULT

0 0 0 FALSE

0 0 1 TRUE

0 1 0 TRUE

0 1 1 FALSE

1 0 0 TRUE

1 0 1 FALSE

1 1 0 FALSE

1 1 1 TRUE