Monday, 25 July 2016

Excel- XOR the exclusive OR

Tea or Coffee? Seems to be a simple question but is the OR inclusive or exclusive? Can you have both tea and coffee? Or are you being offered the option of having a drink and it can be either tea or coffee but not both. In practice, it's an inferred exclusive OR as you are given a cup and saucer and offered a choice of drinks. Maybe you can have both if they come round again.

Tea or Coffee?

Milk and Sugar? Logically speaking, this question is not an AND, this is an OR. An inclusive OR; you can have milk or you can have sugar or you can have both. Struggling with Excel's logical functions AND, OR and NOT for our IF formulas is hours of fun and the exclusive OR condition is one of the hardest to formulate. In a two horse race, one of the two will win but not both.

This one or that one, but not both

Microsoft Excel 2013 introduced the XOR function which now completes the set of logical functions. Although it's somewhat specialised, you can now easily express the exclusive OR condition.

In its simplest and most useful form XOR contains two logical arguments and returns TRUE if either argument evaluates to TRUE. If both arguments are TRUE or neither is TRUE, XOR returns FALSE.

Where more than two logical arguments are added, XOR returns TRUE if an odd number of arguments evaluate to TRUE. And FALSE if an even number of arguments evaluate to TRUE. FALSE if all are FALSE.

10% discount if the Product is Soap or if the Country is UK.

In the above example, we calculate 10% of the Value column C if either the Country in column A is "UK" or if the Product in column B is "Soap". But not when the Country is "UK" and the Product is "Soap". One or the other but not both. Using XOR the formula is (fairly) straightforward:

=IF(XOR(A2="UK",B2="Soap"),C2*10%,0)

The OR function is no good for this calculation as the 10% discount would be calculated where both the Country and Product logical tests were TRUE:

=IF(OR(A2="UK",B2="Soap"),C2*10%,0)

Without an XOR function and using only the IF, NOT, AND and OR functions the formula would have to be entered as follows:

=IF(AND(OR(A2="UK",B2="Soap"),NOT(AND(A2="UK",B2="Soap"))),C2*10%,0)

Related Posts