Microsoft Office Tutorials and References
In Depth Information
Using OR or XOR to Check Whether One or More Conditions Are Met
Instead of using the AND function, you can multiply the conditions.
=(A6=I6)*(B6=J6)*(C6=K6)*(D6*L6)*(E6=M6) will return 1 if all the
conditions are true and 0 if any one of the conditions is false. Altern-
atively, you can type =AND(A6:E6=I6:M6)
=AND(A6:E6=I6:M6) and press Ctrl+Shift+Enter
to have AND evaluate the array of comparisons.
Using OOR oor XOR
to Check Whether One or More Conditions Are Met
In the earlier examples, all the conditions had to be met for the IF function to
be true. In other cases, you might need to identify when exactly one condition
is true, or when one or more conditions are true.
For example, a sales manager may want to reward big orders and orders from
new customers. The manager may offer a commission bonus if the order is more
than $50,000 or if the customer is a new customer this year. The bonus is
awarded if either condition is true. But only one bonus is paid; you do not give
two bonuses if a customer is both new and the order is large. In this case, you
would use the OR function with logical tests to check whether the customer
is new or if the order is large.
To test whether a particular sale meets either condition, use the OR function.
The OR function returns TRUE if any condition is TRUE and returns FALSE if
none of the conditions are TRUE.
In another example, a crating bill of lading is correct if the crate contains
one dog or one cat. If the crate is empty, or if the crate contains both a dog
and a cat, the crate is invalid. In this case, you would use XOR to test for an
Exclusive Or function.
To test whether a particular record matches exactly one condition, use the
XOR to Check Whether One or More Conditions Are Met
The OR function checks whether any of the arguments are TRUE. It returns a
FALSE only if all the arguments are FALSE. If any argument is TRUE, the
function returns TRUE.