Volume Discount Tiers with IFS
Excel version: IFS needs Excel 2019 or Microsoft 365; nested IF functions work in every version.
Your task: In D2, return each order's discount rate from the rules table, then fill it down.
Full instructions, scenario and goal
Scenario
A wholesaler gives volume discounts. The rules are in the small table in F1:G5: an order total at or above a threshold earns that row's discount rate, and the highest threshold reached wins. Sales needs the discount rate for each of today's eight orders, and the rules table may change next quarter.
Your task
- Read the order totals in C2:C9 and the discount rules in F1:G5.
- In D2, write a formula that returns the discount rate for the order total in C2. Use the rules table, not typed numbers.
- Press Enter, then copy D2 (Ctrl+C) and paste it into D3:D9 so every order has its discount.
- Select Check Answer. Pay attention to orders that are exactly on a threshold.
You are done when
- Every cell in D2:D9 contains a formula that gives the right discount rate.
- The formula reads the thresholds and rates from the rules table.
- Orders exactly on a threshold get that threshold's rate.
What you will practise
Choose between several results with IFS, order the tests correctly, and handle values that sit exactly on a boundary.
The idea in 30 seconds
IFS tests a list of conditions in order and returns the result that belongs to the first condition that is true. You write pairs: a test, then the result for that test. It replaces long nests of IF, so a rule with four outcomes stays readable.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell D2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
IFS tests a list of conditions in order and returns the result that belongs to the first condition that is true. You write pairs: a test, then the result for that test. It replaces long nests of IF, so a rule with four outcomes stays readable.
Order matters. Because IFS stops at the first true test, the tests must go from the most specific to the most general, or from the largest threshold down. IFS also returns #N/A when no test is true, so a last test that is always true works as an 'otherwise' result. Finally, check the boundaries: a test with > leaves the threshold itself out, while >= includes it.
Skills you will practise
- IFS
- IF
- Tiers
- Comparison operators
Common mistakes to avoid
- Using > instead of >=
- A total of exactly 500 is not greater than 500, so it falls into the lower tier. Thresholds normally include the amount itself.
- Testing the lowest threshold first
- Every large order is also above the lowest threshold. IFS stops at the first true test, so the lowest tier would win every time.
- Typing the rates into the formula
- The rules are in the sheet so that they can change. Typed rates would keep giving last quarter's answer.