How to Use Multiple Logical Functions in MS Excel

1. Make a list of all conditions to test for in the correct order. For example, in a scenario where a customer who spends $100 or more receives a 20 percent discount if they are a member of your store, but only 10 percent if they are not a member, the conditions to test for are has the customer spent $100 or more and is the customer a member?
2. Decide which of the six functions you will need to use. See the link in the Resources section for a description of each function and what it returns. In our example, we need to use the IF function to perform a logical test. The AND function will be nested in the IF function to test whether the customer has spent at least $100 and if he is a member. The OR function will determine if the customer has met one of the conditions to qualify for a discount.
3. Structure the formula beginning with the IF statement and nest the other functions. In the example above, set up the worksheet as follows: enter 20 percent in cell B1 for membership discount, 10 percent in cell B2 for non-membership discount, and $100 for the minimum purchase in cell B3. List the names of the customers starting in cell A6, each customer's purchase amount in B6 and Membership status ('Yes' or 'No') in C6.
4. Enter the following formula in D6: =IF((AND(C6='Yes',B6>=$B$3)),B6*$B$1, IF((OR(B6>=$B$3)),B6*$B$2,'No Discount')))
This AND function tests whether the customer is a member and if she has spent at least $100. If both conditions are true, multiply the purchase amount by 20 percent to determine what the discount is. The OR function tests whether the customer has spent at least $100 even if she is not a member. If she meets this condition, multiply the purchase amount by 10 percent. If none of the conditions are true, the cell will display 'No Discount.'