AND and OR in one test
PremiumDelivery is free only inside DK, and then only for orders of 500 or more OR for members. In E2, use IF with AND, and OR inside the AND, to show "Free" or "Paid".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Country | Order total | Member | Free delivery? | |
| 2 | Ane | DK | 320 | Yes | ||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock AND and OR and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
Read the rule as "DK AND (big order OR member)". The OR is TRUE because Ane is a member, even though 320 is under 500. B2="DK" is TRUE too, so the AND is TRUE and IF shows Free. A member in Sweden would get Paid, because the AND fails on the country.
Common mistake: Putting all three tests in one AND, or all three in one OR. The rule has two levels, so it needs OR inside AND.
The answer
- E2
=IF(1AND(B2="DK",OR(C2>=500,D2="Yes")),2"Free",3"Paid")
In plain words: 1If the country in B2 is DK and either the order is 500 or more or the customer is a member, 2delivery is Free. 3Otherwise it is Paid.