Excel IF Practice Question: Discount rules: 20% if quantity is 100 or more and the customer is Gold; 10%…
The question
Discount rules: 20% if quantity is 100 or more and the customer is Gold; 10% if only one of those is true; otherwise 0. Show the discount (as a decimal) in D2 to D5.
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Qty | Tier | Discount |
| 2 | O-1 | 120 | Gold | ? |
| 3 | O-2 | 120 | Silver | ? |
| 4 | O-3 | 40 | Gold | ? |
| 5 | O-4 | 40 | Silver | ? |
The yellow cells are where your answer goes.
Show answer
Expected results:
- D2 = 0.2
- D3 = 0.1
- D4 = 0.1
- D5 = 0
How it works
Explanation: Check the strictest rule first (AND). If that fails, check the looser rule (OR). Order matters because the first true test wins.
Syntax: =IF(AND(B2>=100,C2="Gold"),0.2,IF(OR(B2>=100,C2="Gold"),0.1,0))
O-1: both true, so 0.2. O-2: only quantity qualifies, so 0.1. O-3: only Gold, so 0.1. O-4: neither, so 0.
Try it yourself in the live sheet
Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.
Try this question in the live sheet →IF syntax
Returns one value if a condition is true, another if false. Flagging an employee as Pass or Fail based on a score cutoff. Full IF guide.
More IF practice questions
- Using IF, show Pass if the marks are 40 or more, otherwise Fail. Fill C2 to C4.
- Show Reorder when the stock is below 20 and OK otherwise. Fill C2 to C4.
- Using IF, give a 5% bonus on sales above 50000 and 0 otherwise. Fill C2 and C3.
- A candidate is Eligible only if the age is 18 or above and the score is 60 or above.…
- Using IF with OR, show Free if the order is 1000 or more or the customer is a member,…
- A blank Days cell means the order is not delivered yet. Show Done if Days has a value…
- Using nested IF, assign grades: 90 and above is A, 75 to 89 is B, 50 to 74 is C, below…
- Using nested IF with these simplified slabs, find the tax: nothing up to 250000; 5% of…
All practice questions · Excel exercises · Interview prep · Cheat sheet