The XOR function in Excel is a logical function that returns TRUE if an odd number of the arguments are TRUE, and FALSE if an even number of the arguments are TRUE. This function is useful when you need to perform exclusive logical tests, where you want to know if exactly one of multiple conditions is met, but not all. The XOR function is particularly helpful in scenarios where multiple conditions need to be evaluated together in a mutually exclusive manner.
Syntax
| Formula | Description |
|---|---|
| XOR(logical1, [logical2], ...) | Returns TRUE if an odd number of the logical arguments evaluate to TRUE, otherwise returns FALSE. |
- logical1, [logical2], ...: These are the conditions or logical expressions you want to test. You can include multiple conditions.
Example
Consider a scenario where you need to evaluate whether either of two employees, Alice or Bob, worked overtime, but not both. We have their overtime status for three different days.
| Day | Alice Overtime (TRUE/FALSE) | Bob Overtime (TRUE/FALSE) | Exclusive Overtime (XOR) |
|---|---|---|---|
| Monday | TRUE | FALSE | =XOR(TRUE, FALSE) |
| Tuesday | TRUE | TRUE | =XOR(TRUE, TRUE) |
| Wednesday | FALSE | TRUE | =XOR(FALSE, TRUE) |
In this example, the XOR function will return TRUE if exactly one of the conditions (Alice or Bob working overtime) is TRUE, and FALSE if both or neither are TRUE.
Practice Exercise 1
Task: Use the XOR function to determine if exactly one of the two sensors in a factory triggers an alert. The sensor readings for three different machines are given below.
Hint: The XOR function will help you identify if exactly one of the sensors is triggered per machine.
Practice Exercise 2
Task: Use the XOR function to evaluate the test results of three students across two different subjects to determine if exactly one of them passed both subjects. The test results are provided below.
Hint: Use the XOR function to check if exactly one student passed both subjects.
Solution for Exercise 1:
=XOR(B2,C2)
=XOR(B3,C3)
=XOR(B4,C4)Explanation: For Machine A, Sensor 1 triggered an alert (TRUE) and Sensor 2 did not (FALSE). The XOR function returns TRUE, indicating that exactly one sensor was triggered.
Solution for Exercise 2:
=XOR(AND(B2, C2), AND(B2, C2), AND(B2, C2))
=XOR(AND(B3, C3), AND(B3, C3), AND(B3, C3))
=XOR(AND(B4, C4), AND(B4, C4), AND(B4, C4))Explanation: This formula uses the AND function within XOR to check if exactly one student passed both subjects. The XOR function then evaluates if only one condition (a student passing both subjects) is TRUE among the students.