What is XOR Function in Google Sheets?
XOR is a logical operation in Google Sheets that stands for Exclusive OR. XOR has many applications in spreadsheets, such as error checking and conditional analysis. The XOR function in Google Sheets returns TRUE or FALSE, depending on the logical arguments supplied. The XOR function in Google Sheets tests the logical expressions given if an odd number of its inputs are true. If there is an odd number of TRUE values, it returns TRUE. If there is an even number of TRUE or no true values, it returns FALSE.
The simple example below shows two TRUEs and one FALSE in the sheets below. Use the XOR function and observe the result. The cells have an even number of TRUE; hence, it returns FALSE.
Key Takeaways
- XOR is a logical operation that stands for Exclusive OR. If there is an odd number of true values, it returns TRUE. If there is an even number of TRUE values, it returns FALSE.
- The syntax of the XOR in Google Sheets is as follows:
=XOR(logical1, logical2, …)
Here, logical1, Logical2, etc. is an expression or reference to a cell containing an expression representing a logical value like TRUE or FALSE.
- XOR is very useful when you want to determine if there are an odd or even number of specific values.
If both arguments supplied are TRUE, XOR will return FALSE.
Syntax
XOR is useful when checking for mutual exclusivity between two or more conditions. Before we proceed, let’s look at the XOR in the Google Sheets formula.
=XOR(logical_expression1, [logical_expression2, …])
– logical_expression1, logical_expression2, …: Logical expressions or cell references of logical values (`TRUE` or `FALSE`).
How to use XOR Function in Google Sheets?
The XOR function is a pre-defined function in Google Sheets, which performs the function of Exclusive OR. It can be entered in two ways in Google Sheets.
- Enter XOR manually
- Through the Google Menu bar
Entering the XOR Function manually
We have a sample table with some logical values to see the above in action. The goal is to find the exclusive OR of these values, which is the opposite of OR. Here’s how you can do that with XOR.
Step 1: Prepare your data. In this example, select cell D2 as the first cell where you want to output the XOR results.
Step 2: Input the function by entering =XOR(, followed by the logical expressions separated by commas. Since we already have the values, just use the cell references. Close the braces.
Step 3: Press Enter. Grab the fill handle and drop it into the cells below to manually get the results for the entire table.
Using the Google Menu bar
The same function can be entered through the Google menu bar by accessing the following.
- Choose the cell where you want to enter the formula.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ Logical ➝ “XOR.”
Once the function is entered in Google Sheets, enter the required arguments and press Enter.
Examples
In this interesting example, some participants register for a two-day baking workshop. The participants who register for Day 1 need not register for Day 2 and vice versa. Each participant must choose only one day; selecting both days is considered invalid.
Example #1
In this interesting example, we have some participants registering for a baking workshop conducted for two days. The participants who registered for Day 1 need not register for Day 2 and vice versa. Each participant must choose only one day; selecting both days is considered invalid.
Step 1: Let us use the XOR function to validate each participant’s choice. We should check whether the participant has chosen only one day among the two.
Set up the data table with the participant’s names and their choices for Day 1 or Day 2.
Step 2: Now, let us validate the choices. In D2, type the following formula.
=IF(XOR(B2, C2), “Valid”, “Invalid”)
This inner XOR formula will return TRUE if the participant has chosen exactly one day and FALSE if the participant has chosen both sessions or none. The outer IF function then prints Valid for a TRUE result and Invalid for a FALSE result.
Step 3: Drag the formula from D2 to D10 to apply it to all participant entries.
Example #2 – Conditional Formatting with XOR Function
In this interesting example, we have a list of fruits and vegetables and the number of items sold. We must find those Fruits sold more than 50 or those vegetables sold less than 50 and highlight them to balance out the stock. Let us follow the below steps.
Step 1: Enter all the details in a spreadsheet, as shown below. Let’s use the XOR function to check for fruits that have sold more than 50 or vegetables that have sold less than 50 and highlight them to balance out the stock.
Step 2: Now, in E2, type the following formula.
=XOR(B2=”Fruit”,C2<50)
This XOR formula will return TRUE if the item Fruit has sold more than 50 times and FALSE if vegetables have sold less than 50 times. Press Enter.
Step 3: Drag the formula from D2 to D9 to apply it to all entries.
Step 4: Go to Format > Conditional Formatting. It is to apply conditional formatting.
Step 5: You get a Conditional Format rules pane on the right.
Here, select the range A2:D9. Now, go to “Format cells if.” Here, choose “Custom Formula Is.” In the box that appears, enter the following formula:
=$D2=TRUE.
For the formatting options, choose the color of your choice. This shades all the rows according to the required condition.
Example 3 – Using XOR with IF Function
Let us check if some students have attended an odd number or even number of days of a seminar using the IF function and apply conditional formatting to the cells to visually highlight the responses that are odd.
Step 1: Let us use the XOR function to check if the students have attended for an odd or even number of days. Set up the data in a table.
Step 2: Now, let us validate the number of days. In E2, type the following formula.
=IF(XOR(B2, C2, D2), “Odd”, “Even”)
This inner XOR formula will return TRUE if the student has attended odd number of days, and FALSE if they have attended even number of days. The outer IF function then prints Valid for a TRUE result and Invalid for a FALSE result.
Step 3: Drag the formula from D2 to D8. It is to apply it to all student entries.
Step 4: To apply conditional formatting, go to Format > Conditional Formatting.
Step 5: You get a Conditional Format rules pane on the right.
Here, select the range A1:E8. Now, go to “Format cells if.” Here, choose “Custom Formula Is.” In the box that appears, enter the following formula:
=($E:$E=”Odd”). This shades all rows which have “Odd” in Column E.
For the formatting options, choose the color of your choice.
Press Done.
Important Things to Note
- When XOR is used in a formula where the referenced cells are blank, it can lead to unexpected results.
- If we supply XOR with a single parameter, the output will be the original test results. For instance, =XOR(FALSE) gives FALSE, while =XOR(TRUE) gives TRUE.
- XOR is often used with checkboxes as they return TRUE or FALSE values.
- Using XOR for complex formulas by combining them with AND or OR without being clear about XOR’s functionality can result in logic errors.
Frequently Asked Questions (FAQs)
The XOR function is used for checking many logical conditions
1. It is used in conditional analysis. It enables complex conditional checks within data sets, even where mutually exclusive conditions must be evaluated.
2. It is very useful for error checking where conditions are expected to be exclusive.
3. It simplifies the creation of complex logical formulas that would otherwise need multiple IF statements
4. In data validation, it ensures that mutually exclusive options are handled correctly.
Some of the common mistakes include:
1. Not inputting the correct data type as arguments.
2. Misunderstanding the correct XOR logic
3. Applying XOR in overly complex formulas
Let us look at the basic differences between OR and XOR in Google Sheets.
XOR: It returns TRUE when there is an odd number of TRUE values. If there is an even number of TRUE or no true values, it returns FALSE.
OR: Returns TRUE if any of the conditions are TRUE, if all conditions are FALSE, only then it returns FALSE.
Download Template
This article must help understand XOR in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is XOR in Google Sheets. We learn its syntax & how to use it for Exclusive OR calculations with examples & working template. You can learn more from the following articles. –
Leave a Reply