FinanceTier 420 minintermediate

Discount Tier from a Purchase Volume Table

Assign the correct discount tier from a threshold table instead of a wall of nested IFs.

What you will learn

• Build an approximate-match lookup against a threshold table sorted ascending
• Test the boundary values that sit exactly on a threshold
• Explain why an exact-match lookup fails for every volume that is not one of the listed thresholds

Excel skills

VLOOKUP with TRUE, XLOOKUP match_mode -1 · Lookups

The task

Discount is set by volume, from the Bands tab: from R0 it is 0%, from R10 000 it is 5%, from R50 000 it is 10%, and from R150 000 it is 15%. "From" means the threshold itself is in the higher band. Work out each customer's discount. The bands change over time, so the rate must come from the Bands tab, not be typed into a formula.

You work in a real spreadsheet in your browser, and your answer is checked against hidden traps the moment you submit.

Try this lesson free