XOR Function
It returns TRUE when one of the conditions is met and FALSE when neither or both conditions are fulfilled.
What is the XOR Function?
The XOR function returns TRUE when one of the conditions is met and FALSE when neither or both conditions are fulfilled.
To understand the function better, let's consider mutually exclusive investment decisions. These decisions are undertaken during capital investments when you have a limited budget, which allows you to invest only in either of the two projects but not both.
If you invest in project A, you won't have enough funds to invest in project B and vice versa.
If you compare the criteria of those investment decisions with the function, which is a logical 'exclusive OR' function, if one of the conditions is met, then the function returns the result as TRUE similar to one investment criteria for mutually exclusive decisions.
These decisions do not allow for both investments, whereas the XOR function does not allow the fulfillment of both conditions and returns the result as FALSE.
Finally, at least one investment must be made during mutually exclusive investment decisions(since no assets would be a waste of time). At the same time, the XOR function also returns FALSE if not a single logical condition is fulfilled.
This article will explain the function, its syntax, and a couple of examples.
Key Takeaways
- The XOR function is a logical function that returns TRUE when one condition is met and FALSE if both or neither conditions are fulfilled.
- It acts as an 'exclusive OR' function for two logical statements where only one condition must be fulfilled but not both.
- The function can accept 255 additional arguments apart from the required argument.
- Excel's function exhibits peculiar behavior when you input more than two logical conditions.
- If the number of conditions fulfilled is odd, the function returns the result as TRUE, while if the number of requirements fulfilled is an even number, the result is FALSE.
- You can use the traditional OR function if you do not want the result to exhibit this erratic behavior.
- The OR function returns the result as TRUE when one or all the conditions are met while returning FALSE only when none of the requirements are fulfilled.
Understanding XOR function
The XOR is categorized as a logical function that returns the boolean value TRUE when either of the conditions is fulfilled and FALSE when none or all the requirements are met.
When you compare the function with the traditional OR operation, the significant difference is that the OR will only return FALSE when none of the conditions are fulfilled.
However, the 'exclusive' XOR function doesn't fulfill all the conditions. For example, if the two conditions are 1 > 0 and 2 > 0, the function will return the result as FALSE since both conditions are evaluated as TRUE.
On the other hand, if you had used the OR function, the function would have returned the result as TRUE due to the fulfillment of both conditions.
The syntax for the XOR function is:
=XOR(logical1,[logical2]...)
where
- logical1 - (required) the first logical condition which will be evaluated
- logical2 - (optional) the second logical condition which will be evaluated
NOTE
You can use up to 255 additional logical statements in the optional function.
One of the most critical components of conditional statements is the logical operators, which can be combined with numbers, text strings, boolean values, and other complex numerical expressions.
The different logical operators that you can use in conditional statements are:
| Condition | Operator | description |
|---|---|---|
| Equal to | = | Compares two values and returns TRUE if they are equal, |
| Not equal to | <> | Compares two values and returns TRUE if they are not equal |
| Greater than | > | If one value is greater than the other, then the condition evaluates to TRUE. |
| Less than | < | If one value is less than the other, then the condition evaluates to TRUE |
| Greater than or equal to | >= | The condition returns as TRUE if the value is greater than or equal to another value. |
| Less than or equal to | <= | The condition returns as TRUE if the value is less than or equal to another value. |
How to use the XOR function
The function can be accessed from the library or used as a worksheet formula. You can access the function's library if you are a beginner looking to build up valuable information on understanding the syntax for different parts and how they work.
Click on Formulas, and you will find all the categories of different functions. Next, you can select whatever part you intend to look at from the drop-down menu. For example, you would find the VLOOKUP function in the Lookup & Reference category.
One of the most common methods of using the formula is a worksheet formula. For example, suppose that you have the data as illustrated below:
By using the formula =XOR(B3>0, C3>0) in cell D3, we get the result as TRUE. We have two conditions: 2 > 0 and the other is -4 > 0. Our first condition gets fulfilled, while the second does not. Hence, the function returns the result as TRUE.
Let's change the scenario. The data is as below:
When you use the formula =XOR(B3>0, C3>0), both the conditions get fulfilled since both the numbers are more significant than zero, which ultimately returns the result as FALSE.
Finally, if both the conditions fail to be met, such as by using the formula =XOR(B3<0, C3<0), we still get the result as FALSE since both the numbers are not smaller than zero.
The bottom line is that the function will let you select either the 'this' or' that' value but not both.
Examples of the XOR function
The function displays certain erratic behaviors, making it an excellent data analysis tool.
This section will explore that erratic behavior and see examples of the function.
a) Example #1: Two logical conditions
Suppose you are looking to divide a bunch of students into two groups based on their test scores.
The data for the test scores are illustrated below:
The XOR function will allow us to categorize the students into two groups based on the three outputs that the function return.
The conditions that will categorize the students into groups are:
- Students with a test score greater than 50 or female will be assigned to group A.
- Students with test scores greater than 50 and females will be given to group B.
- Students with neither test scores greater than 50 nor males will be assigned to group B.
We will use the formula =IF(OR(C3>50,D3="Female"),"Group A","Group B"), which gives the result:
The function provides the third dimension by returning the result as FALSE when both conditions are fulfilled, as illustrated above by assigning the students' Group B'
b) Example #2: More than two logical conditions
We spoke earlier about how sometimes the XOR function might function erratically when you use it in Excel. What did we mean by that?
When you input more than two conditions in the function, the function will return the result as TRUE when the total number of requirements fulfilled is odd.
Similarly, if the total number of conditions fulfilled is an even number, the function returns the result as FALSE.
For example, let's say you input five conditions in the formula. If all five conditions are fulfilled, the result is TRUE, and if two conditions are fulfilled, the result is FALSE.
With that being said, let's see an example to understand this behavior exhibited by the function. Suppose you have the data as illustrated below:
By using the formula =XOR(C3>35,D3>35,E3>35) in cell F3 and dragging it down to cell F12, we get the result:
Whenever the number of conditions fulfilled is odd, we get the result as TRUE, whereas when the number of requirements fulfilled is an even number, we get the result as FALSE.
Thus, the function can become a bit complicated to use in different scenarios due to the result that it returns. However, if you consider it, you can always find situations where XOR might make the best tool to get the most accurate answer.
XOR vs. OR function
The OR is categorized as a logical function that returns TRUE when one of the conditions is met and FALSE when all the conditions fail to satisfy.
The most significant difference between both functions is that OR will return the result as TRUE when all the conditions are fulfilled, while the XOR function would return the result as FALSE.
Even though the difference between both functions looks minute, it is, in reality, huge considering the change in the dynamics of the results.
Both functions are used to evaluate multiple conditions and find whether one or more conditions are fulfilled.
The syntax for the OR function is
=OR(logical1,[logical2]...)
where
- logical1 - (required) the first logical condition which will be evaluated
- logical2 - (optional) the second logical condition which will be evaluated
NOTE
You can use up to 255 optional logical conditions in the function apart from the required argument.
Let's see an example to compare the results returned from both functions. Suppose you have the test scores for the students, as illustrated below:
We need to evaluate if any of the students have failed in at least one of the subjects out of the three. The passing score on each of those tests is 35.
First, we will use the formula =IF(OR(C3>35, D3>35), "Pass", "Re-assign the test") in cell F3 and drag it down to cell F12, which gives the result.
Even though we have some of the test scores below 35, as in the case of 'April Jones', the other test still has a score greater than 35. Thus giving us the final result as' Pass.' However, if both tests have scores below 35, we get the result 'Re-assign the test.'
On the other hand, by using the formula =IF(XOR(C3>35,D3>35), "Pass", "Re-assign the test") in cell F3 and dragging it down to cell F12, we get the result:
The first result itself we see is 'Re-assign the test.' Why should the student even give the test again if he has already scored more than the cutoff marks?
But that's how the function works; conditions were fulfilled, so the process returned the result as FALSE. On the other hand, if one of the conditions was met, the function returned the result as 'Pass' or else it was always 'Re-assign the test.'
or Want to Sign up with your social account?