How to Use the XOR Function in Google Sheets
XOR is a logical operation that stands for Exclusive OR. XOR has many uses in programming, but it’s also useful in spreadsheet apps such as Google Sheets. The XOR function in Google Sheets returns either TRUE or FALSE, depending on the logical operation.
This function is a bit more difficult to grasp than other functions in Google Sheets, but don’t worry. We’re going to break down XOR, explain how it works, and how you may use it in Google Sheets.
Syntax of the XOR Function: How Does It Work?
XOR is a logical function that takes in one or multiple logical expressions, tests them, and then returns TRUE or FALSE.
The XOR function in Google Sheets tests the logical expressions that you feed it, and second, it tests if an odd number of its inputs are true. If there is an odd number of true values, then it returns TRUE. If there is an even number of true values or no true values at all, then it returns FALSE.
To use the XOR function in Google Sheets, call the function and provide a logical expression as an argument. This logical test can be anything, and you may refer to cells in your test as well.
In the spreadsheet above, you can see XOR’s reaction to different tests. Let’s break this down:
If you feed XOR a number rather than a logical test, XOR will consider it a true statement. For instance, feeding XOR just the number 1 will return TRUE because there is one true statement.
By now, you could probably guess what the formula below will output:
In this formula, we’ve given XOR four numbers. These aren’t tests, so they’re always true. Thence, XOR looks through the input, finds four true statements, and returnsFALSEbecause four is an even number.
XOR is a peculiar function since it accounts for the count of true statements in addition to the statements themselves. When you give it a single test, XOR’s result will be a direct reflection of the logical test’s result. Given multiple tests, XOR’s output depends on the number of true statements.
Using the XOR Function in Google Sheets With a Single Parameter
In a way, an XOR function with a single test is similar to theIF function in Google Sheets, except that instead of custom outputs, it returns TRUE or FALSE. This way, we can use XOR to determine whether a value meets a specific condition or not.
To see that in action, we have a sample spreadsheet here with a column of numbers. The goal is to determine whether each number is above the total average of the numbers, or not. Here’s how you can do that with XOR:
Now XOR will tell you if the number meets the specified condition or not. If the number is above the average, it will return TRUE, otherwise it will return FALSE.
The formula utilizes both XOR and the AVERAGE function. It calls onXOR, feeds it the cell (A2), and then asks it to test whether the cell is bigger thanAVERAGE(A2:A14), which is the average of all the numbers. If the cell is above the average, you’ll have one true statement. One is an odd number, and thus, XOR will outputTRUE.
Using the XOR Function in Google Sheets With Multiple Parameters
You can clearly observe XOR’s sensitivity to the number of true statements when you feed it more than one parameter. With multiple parameters, XOR will go through the statements and return TRUE if there is an odd number of true statements. Otherwise, it will return FALSE.
This characteristic of XOR makes it useful when we want to determine whether we have an odd or even number of specific values. Let’s see this with an example.
In this sample spreadsheet, we have five columns dedicated to strings of numbers. The goal here is to use the XOR function to determine if there is an odd count of numbers in each sequence.
XOR will now tell you if each string has an odd count of numbers in it or not. Recall that numbers are true statements, and XOR returns TRUE if there is an odd number of true statements.
In the formula,XORlooks through cellsA2toE2. There are no tests here as we didn’t specify a condition. XOR will only take in the available numbers in that range and returnTRUEif the number count is odd. If the count is even, XOR will outputFALSE.
Real-Life Examples of XOR in Google Sheets
You can look up XOR in any Google Sheets scenario where the count of specific values being even or odd is vital. Here are two examples to give you an idea of what these scenarios might look like in real life.
XOR Function Example 1: Football Teams
In this scenario, we have four football teams in a league, and each team has played two matches. Naturally, the team that has won both matches takes the first place, and the team that has lost both matches takes the last place.
The teams that have won a match and lost another, will do a third match between them to determine the second and third place. The goal is to determine which teams will do a third match.
You can easily achieve this goal with the XOR function:
The formula calls onXORto look through the match results (B2toC2) and see how many equalWin. If the team has won zero or two matches, XOR outputsFALSEbecause zero and two are both even numbers. If the team has won only one match, then XOR outputsTRUEbecause one is an odd number.
Note that since you want XOR to test the cells in that range individually, you need to put the core formula inside anARRAYFORMULA function in Google Sheets.
XOR Function Example 2: Dinner Party Attendants
In this scenario, we have a list of guests attending a dinner party, and whether they’ll be bringing a companion. The host already has a rectangular dinner table, but he’s trying to figure out whether he should use a round table instead.
Since a rectangle has four edges, only an even number of people can evenly sit around it. A circle, on the other hand, allows an odd number of people to sit around it as well.
You can help the host make a decision by using the XOR function:
To grasp this formula, you need to recall three mathematical laws:
These laws mean that the sum of the guests who are bringing a companion and their companions is an even number, because it’s simply their count multiplied by two.
Therefore, if there is an even number of guests without companions, then the total number of guests will be even. However, if there’s an odd number of guests without companions, then the total number of guests will be odd.
With this knowledge, you can use XOR to check the count of single guests. If the count is odd, XOR will outputTRUE. This means that the host will need to use a round dinner table!
Even or Odd? Ask XOR!
The XOR function in Google Sheets takes in a user-specified condition, and if there is an odd number of values that meet the condition, it returns TRUE. Otherwise, it returns FALSE.
If you give XOR a single parameter, XOR’s output will be the test results. But with multiple parameters, the count of true statements will determine XOR’s output. Now that you know what XOR is and how it works, it’s time to turn this knowledge into mastery by trying it out yourself!
If you want to make sure only certain data can be added to a cell, start restricting entries with dropdown lists in Google Sheets.
it’s possible to’t call this offline, Notion.
If an AI can roast you, it can also prep you for emergencies.
Obsidian finally feels complete.
Unlock a world of entertainment possibilities with this clever TV hack.
you could block out the constant surveillance and restore your privacy with a few quick changes.
