The XOR function in Google Sheets lets you check if the data you input in the spreadsheet meets the specific criteria.
If the data correlates to the given criteria, the XOR’s return value will be TRUE and FALSE if it does not meet the requirements.
Table of Contents
The rules for
XOR function in Google Sheets are as follows:
- 0 is equivalent to FALSE.
- NEGATIVE NUMBERS’ return value is equivalent to TRUE
- ALL NUMBERS is equivalent to TRUE
- TRUE ODD NUMBER argument is equal to a TRUE return.
XORfunction takes the logical value and range parameters.
Let’s have an example.
Choox desperately wants to win the Mobile Legends tournament. He wants to ensure that his team will use the best heroes in their lineup. He doesn’t want his team to use a mage hero with MMR lower than 2000.
Choox then uses the
XOR function to narrow down their heroes’ choices.
Here is the data of his team’s heroes and the conditions that he set.
Within seconds, Choox was able to pull up the best mage hero that they would use.
Choox and his team can use Kadita, Lylia, Pharsa, or Harley.
So, how did Choox generate the result?
We will get there in a few. First, let us understand how the function works. Let us start with its parts.
The Anatomy of the XOR Function
So the syntax (the way we write) the
XOR function is as follows:
=XOR(logical_expression1, [logical_expression2, …])
Let’s dissect the
XOR function’s parts and understand its meaning:
- = the equal sign is just how we start any function in Google Sheets.
- XOR() this is our
XORfunction. XOR will determine the value if it corresponds to the given criteria.
- Logical_expression1 serves as the first reference value of the cells containing the logical value True or False. The logical_expression1 is the first condition.
- Logical_expression2 and additional expressions are the additional reference cells containing the logical value True or False. The logical_expression2 and the other expressions are the second and additional conditions that you need to get the returning value.
- Returning value is the result of the cells containing logical values. The returning value can be TRUE or FALSE, depending on the criteria set.
Note: Logical Expressions or conditions can verify whether certain data is greater than, smaller than, or equal to the desired criteria. If it is greater than or equal to the condition set, the returning value is TRUE. If it is lesser than or is not equal to the criteria given, the returning value is FALSE.
A Real Example of Using XOR Function
Here’s a detailed example of how we use the
XOR function above.
As you can see, the
XOR function automatically determines the value of specific cells. The example above determines if the hero is a mage or has less than 2,000 MMR. If it meets at least one of the conditions, the returning value is TRUE. If it did not meet any of the conditions, the returning value is FALSE.
On the screenshot above, the formula on how to get the returning value is also shown.
You may copy the example spreadsheet above by clicking on the link below.
How to Use XOR Function in Google Sheets
1. Set the conditions first. Before you input the data and the formula, you must answer this guide question: “What are the conditions I need to set to get the correct filter of the data?”
In the example above, the conditions are as follows:
- The hero must be a mage.
- The hero must have an MMR of less than 2000 MMR.
Once you have the conditions listed, you may proceed to add the data to the spreadsheet.
2. Next, after adding the data to the spreadsheet, start working on the formula.
Always remember, start the formula with an ‘=‘ sign.
3. After the
XOR function ‘=XOR(‘, add the specific cells you need to get a returning value from.
4. After selecting or adding the cell, add the first condition. In this example, the first condition is, the hero type should be a mage.
When you input the first condition, make sure to add the ‘=‘ sign right after the selected cell. The equal sign triggers the function to determine whether or not the cell meets the first condition.
After adding the ‘=‘ sign, add the ‘“‘ quote symbol, then add the first condition. Close the first condition with another “““ quote symbol.
Add comma ‘,‘ to let the system know that there is another condition that you need to add.
In this step, the XOR function should look like what’s on the picture.
5. Before adding the second condition, select the cell that you need a returning value with. In this example, D5 is our reference cell for the second condition.
6. Now that you have added the second cell, add the second condition. In this example, our second condition is that the MMR must be less than 2000.
Please take note of the difference. We used the ‘=‘ sign on the first condition because we needed to find the mage-type heroes alone. The second condition, however, used the ‘<‘ sign. This indicates that for our second condition’s returning value to be true, the MMR should be less than 2000.
7. Here are the complete returning values:
The hero must be a mage with an MMR below 2,000 HP.
Here are the returning values from the data above.
Kadita, Lylia, Pharsa, and Harley are all mages which makes the returning value TRUE. Franco, Layla, and Alucard’s value, also yields to TRUE. Though they are not mages, their MMR value is under 2000. While Belerick and Badang’s value returns FALSE since they are not mages and their MMR is 2000 or more.
Frequently Asked Questions
1. What are the other ways to derive the returning values using XOR Functions?
There are several formulas that you can use. The example above is the easiest one. Here is the list of the other formulas:
- =XOR(TRUE, FALSE, TRUE)
- =XOR(A2=TRUE, A3=FALSE)
- =XOR(A1:A10, B1:B10)
- =XOR(0, 1, 2, 3)
2. What is the logic behind the
Think of the
XOR function as an “either, or” where you must choose ONLY ONE option. Whichever option you choose makes the returning value TRUE. The option that you did not select renders a FALSE returning value.
3. How many conditions can you make in one
You can make several conditions in one
XOR function. Your returning values will render the correct answer as long as the correct cells are selected and the correct conditions are set.
That’s it for today’s how-to article. You may want to click this link for another Google Sheet spreadsheet formula trick.
Do subscribe to our newsletter to be the first to receive the latest Google Sheets guides and tutorials from us.