How to Use the XOR Function in Google Sheets

Published on

The XOR function in Google Sheets performs an exclusive OR operation. But what exactly does “exclusive OR” (often abbreviated as XOR or EOR) mean?

In logical terms, the XOR function differs from the standard OR function. While OR returns TRUE if any one of the conditions is TRUE, XOR only returns TRUE when an odd number of conditions are evaluated to TRUE.

This feature is what makes XOR “exclusive.” Even if the majority of conditions are TRUE, XOR may still return FALSE if the count of TRUE values is even.

To clarify, here’s how XOR works with examples:

  • Example 1: If there are 10 conditions, and only one evaluates to TRUE, then XOR will return TRUE because the count is odd.
  • Example 2: If 8 conditions evaluate to TRUE and 2 evaluate to FALSE, XOR will return FALSE because the count of TRUE values is even.

XOR Function vs. OR Function: Key Differences

You might wonder when the XOR function is useful compared to the OR function. The key difference is that XOR returns TRUE only when exactly one of the conditions is TRUE in a two-condition scenario, unlike OR, which returns TRUE if either or both conditions are TRUE.

For example:

  • OR(A1, B1) returns TRUE if either or both A1 and B1 are TRUE.
  • XOR(A1, B1) returns TRUE only if one of A1 or B1 is TRUE (but not both).

XOR Function: Syntax and Arguments

XOR(logical_expression1, [logical_expression2, …])
  • logical_expression1: An expression or a cell reference containing a logical value, such as TRUE or FALSE, or an expression that evaluates to a logical value.
  • logical_expression2, …: Additional logical expressions to test.

XOR Formula Examples

Let’s look at some practical examples to understand XOR in Google Sheets:

Example 1: Basic Boolean Test

  1. In cells A1:A10, enter 0s (representing FALSE values).
  2. Use the following formula: =XOR(A1:A10) Since all values are FALSE, this formula will return FALSE.
  3. Now change the value in A10 to 1 (representing TRUE). The formula will now return TRUE because there is one TRUE value, which is an odd count.
  4. If you change another cell (say A9) to 1, the formula will return FALSE because there are now two TRUE values, which is an even count.

In Google Sheets, 0 is interpreted as FALSE, and 1 as TRUE in Boolean logic.

Example 2: Choosing Based on Budget (Practical Scenario)

Suppose you want to buy a smartphone, and your budget is <$1500. You’re comparing prices of two models, with Product 1’s price in cell A1 and Product 2’s price in cell B1.

Scenario:

  • If both phones are either within your budget or over your budget, you’ll flip a coin to decide which one to buy or consider increasing your budget.
  • If only one phone is within budget, you’ll choose the affordable one.

Use this combined formula with the XOR and IF functions to make a decision:

=IF(XOR(A1 <= 1500, B1 <= 1500), MIN(A1, B1), "Flip a coin")
  • This formula returns the price of the cheaper phone if only one of the phones is within budget (since XOR returns TRUE if only one condition is TRUE).
  • If both phones are either within the budget or both are outside the budget, the formula suggests flipping a coin.

Conclusion

The XOR function is a versatile tool for situations where you need to evaluate exclusive conditions. It’s particularly useful in scenarios where only an odd count of conditions should trigger a result, making it a unique choice for Boolean logic in Google Sheets.

That’s all! With XOR, you can add more precision to your decision-making processes in spreadsheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.