 |
| 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
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)
=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)
=IF(AND(OR(A2="UK",B2="Soap"),NOT(AND(A2="UK",B2="Soap"))),C2*10%,0)
No comments:
Post a Comment