What is exclusive OR function?

XOR function or exclusive OR function was introduced in Excel 2013. This function is available under excel logical functions. For the given statement, the XOR function will return true only when either of the statements is true. It returns false when both the statements are true or when both the statements are false.

Syntax: =XOR (logical1, logical2,…… ).

In an XOR function, logical1 is a required argument, whereas other subsequent arguments are optional. A single XOR function can take a maximum of 255 arguments.

Excel logical function

Excel supports four different logical functions to handle the logical values. The logical functions are AND, NOT, OR, and XOR (exclusive OR). Similar to the logical operator, excel logical function will return the result as either false or true (while evaluating the arguments).

How to write XOR function in excel

The easiest way to insert an XOR function in the cell is to, just type the function name with an equal to (=) sign at the beginning. Whenever excel finds the equal to (=) at the beginning of the cell, then it recognizes the user is about use a function.

Steps to insert an XOR function is given below.

• Enter equal to (=) followed by XOR (function name).
• Enter open parentheses.
• Specify the comma-separated list of arguments passed to the function as parameters.
• Close the parentheses and press enter to evaluate XOR function on the supplied arguments.

For example the formula using the XOR function entered in a cell =XOR(3>12, 4>9) in Excel returns true since both the arguments are false.

Important facts of exclusive OR function

• Empty references are ignored by the XOR function.
• When the logical values are not found, then it returns #VALUE! error.
• When the functions are used in an earlier excel version (earlier than excel 2013), then it returns #NAME! error.
• The arguments of the XOR function need to be evaluated to logical values like true, false, array, or reference which contains logical values.

Using exclusive OR function in excel

XOR function will return only logical results. The following are the various scenarios of using XOR function.

When there are multiple logical statements, then the function will return -

• True – when an odd number of arguments results as true.
• False – when an even number of arguments results as false and also when none of the arguments are true.

Comparing numbers with XOR function

There are set of comparison operators used in excel to compare numbers and they are

When an expression passed to the XOR function evaluates to a non-zero number, then XOR will consider that logical value as true and when the expression evaluates to zero, then XOR will consider it as false. The evaluation of XOR for various input combinations are listed in the table below,

Compare text with XOR

The text comparison using XOR is not case-sensitive. XOR function will not support wildcards as it looks for the exact match of the given text string.

Example: The following formulas will produce the same result as text comparison is not case-sensitive. When the data present in cell C2 is equivalent to the given test then it results as true.

=XOR(C2=”FORMULA”).

=XOR(C2=”formula”).

=XOR(C2=”FORmula”).

Examples

Example 1: XOR function with multiple arguments.

Microsoft Excel supports XOR function with multiple arguments and can have a maximum of 255 arguments. The XOR function returns true when an odd number evaluates to true and it returns false when an even number of arguments evaluates to false. To save time and make the work simple excel allows the user to supply a cell range rather than separating each cell reference using commas as shown below.

Formula: =XOR(C4:H4).

Example 2: XOR function with two arguments

Sample problem: Consider a food court owner who has decided to provide bonus to the staff, if the staff sell over $500 of drinks and $500 food within a week. When the staff sells above $500 in both drinks and food, then he will get a full bonus. When the staff sells above $500 in either drinks or food, but not both, then he will get a half bonus. Write the formula for calculating both for values in the cells C3 (sale of food) and D3 (sale of drinks) for Employee3 respectively.

To calculate the full bonus, AND function is used and the formula is given by =AND(C3>=500, D3>=500). This AND function will return true when both the sales on both food (in column C) and drink(in column D) are above $500. Half bonus is calculated using the XOR function. The formula is =XOR(C3>=500, D3>=500). It returns only when either of the arguments is true. Otherwise, it returns false.

Context and Applications

This topic is important for postgraduate and undergraduate courses, particularly for,

• Bachelors in Computer Science Engineering.
• Associate of Science in Computer Science.

Practice Problems

Question 1: What is XOR?

1. Exclusive OR
2. Extended OR
3. Excel XOR function
4. None of the above

Answer: Option a is correct.

Explanation: XOR or exclusive OR is one of the excel logical functions. The purpose of the XOR function is to return true only when either one of the statements is true and returns false when both the statements are true. The XOR function also returns false when both statements are false.

Question 2: What is the formula used to calculate sum?

1. SUMPRODUCT(1,2)
2. =SUM(C3,D3)
3. SUMIF(C3,D3)
4. None of the above

Answer: Option b is correct.

Explanation: The correct formula to calculate sum is =SUM(C3, D3). The structure of the sum function should have equal to, followed by the function name and then the comma-separated list of arguments.

Question 3: Which of the following is an excel logical function?

1. NOT function
2. AND function
3. XOR function
4. All of the above

Answer: Option d is correct.

Explanation: Excel supports four different logical functions to handle the logical values. The logical functions are AND, NOT, OR, and XOR. Similar to the relational operator, excel logical function will return the result as either false or true (while evaluating the arguments).

Question 4: What is the result of =XOR(3>0,2<9)?

1. True
2. False
3. #VALUE! error
4. None of the above

Answer: Option b is correct.

Explanation: The above formula will return the result as false. As the 1^st argument (3>0) is true and the 2nd argument (2<9) is also true, so the result of the XOR function will be false.

Question 5: __ error will be returned when the arguments are not of the expected type.

1. #VALUE! error
2. #NAME! error
3. #SORT! error
4. All the above

Answer: Option a is correct.

Explanation: When the arguments passed to the function are not of boolean type, then #VALUE! error is displayed.