Excel Practice

Tiered commission practice

A marginal tier pays the higher rate only on sales above the threshold. Applying it to all sales overstates commission.

Lesson 57 of 72 · 3 exercises

Practise Tiered commission

What you will practise

MIN caps the lower band. MAX prevents a negative upper band when sales are below the threshold. Each portion earns only its own rate.

MIN(sales, threshold)*base_rate + MAX(sales-threshold, 0)*higher_rate

The syntax above is a guide. Read the data and each task to choose the right arguments for your formula.

3 exercises in this lesson

  1. Exercise 1 · answer in E10

    Calculate commission on C6: base rate B8 on the first B7 of sales, higher rate C8 above it.

  2. Exercise 2 · answer in E10

    Calculate only the higher-band commission for sales C6 above threshold B7.

  3. Exercise 3 · answer in E10

    Calculate the effective commission rate by dividing total tiered commission by C6 sales.

Before you check your answer

  • Keep the source data intact. Write your answer in the highlighted result cell. Changing a source value changes the question instead of solving it.
  • Use the requested cells. Check the first and last row of a range and read its column heading. A plausible total can still come from the wrong data.
  • Reference values that can change. A fixed number may match today's sheet. A cell reference keeps the formula connected when the source values change.

How the answer is checked

The workbook evaluates your formula against the requested result. It also checks the skill being practised and tests the formula with changed source data, so a fixed answer that happens to match is not enough.

Use progressive hints when you need help, then try the task again with fresh data. Correctly solving a step opens the next exercise.