Share in Facebook


05 September 2018

XOR Function


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
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
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
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
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
Using XOR Function in Multiple Conditions in Microsoft Excel


I hope you have enjoyed this article. Take care…
Thank you for reading….