Today I will discuss about the logical ‘XOR’ function in
Microsoft Excel in this article in detail.
XOR
– Exclusive OR function, although not used widely in
Microsoft Excel but sometimes using this function can reduce lot of nested
functions, thus eliminating the complex nested formula by single formula.
 |
| XOR Function in Microsoft Excel |
Where to use XOR Function?
This function is little
complicate to understand at first, therefore, we will discuss it using an example
first then we will discuss it in detail. Suppose, we need to derive the winning teams from
a basketball competition league and the statistics of wining and runners up teams are as below image.
 |
| XOR Function in Microsoft Excel |
The team who has won the
maximum number of matches will qualify for the next level. Therefore, we should
find the team with maximum number of 'WON' word from the five round of
competition and declare that team as the Qualified for the next game.
It can be done in other
ways, e.g., use COUNTIFS function to count the number of ‘WON’ statement
in a row and then declare that team as Qualified if the occurrence of WON word
is 3 or 4 or 5.
But the easiest way to do
it is using XOR function. Those who are little familiar with programming or logic gate in computer components are much comfortable with this concept but there is no harm in reading again.
Let us discuss this function
in details.
XOR returns TRUE if
either statement is TRUE, but returns FALSE if both statements are TRUE. If
neither is TRUE, XOR also returns FALSE.
 |
| XOR Function in Microsoft Excel |
Return value
TRUE or FALSE
Syntax
=XOR (logical1, [logical2], ...)
Parameters or Arguments
logical1 - An expression,
constant, or reference that evaluates to TRUE or FALSE.
logical2 - [optional] An
expression, constant, or reference that evaluates to TRUE or FALSE.
What will be the return type of XOR function?
There will be two
outputs, either TRUE or FALSE.
If two conditions are
TRUE, XOR will return FALSE
If two conditions are
FALSE, XOR will return FALSE
Otherwise XOR will return
TRUE.
These above rules are for such condition where there are TWO conditions
given.
Please note carefully
the below image, the output is TRUE when two conditions' results are TRUE
& FALSE respectively and the output is FALSE when there are results are TRUE &
TRUE or FALSE & FALSE.
 |
| XOR Function in Microsoft Excel |
How to use XOR function in multiple conditions?
When there are multiple
conditions the output of XOR function will be TRUE only WHEN THE NUMBER OF TRUE
CONDITIONS ARE ODD otherwise the output will be FALSE i.e., if the number of
FALSE CONDITIONS ARE EVEN the XOR output will be FALSE.
Please follow the below
image which will clarify this situation easily.
|
| Using XOR Function in Multiple Conditions in Microsoft Excel |
I hope you have
enjoyed this article. Take care…
Thank you for reading….
