Tiered Rates: The Commission Run Paid 99,398.40 on a Plan That Says 50,398.40

The Q3 commission run went out on the last Friday of the quarter. Ten reps, 1,389,990.00 of sales, 99,398.40 of commission — 7.15% of everything sold. Every rate in it was the correct rate from the plan's rate card, every multiplication was right, and nobody had typed a number over a formula.

Read the same rate card the other way and the same ten reps are owed 50,398.40. The 49,000.00 in between is not a formula error. It is one sentence in a commission plan that can be read two ways, and a spreadsheet will implement either reading without ever suggesting there was a choice.

This is what a tiered rate is: a rule that changes rate at a threshold. Commission ladders, income tax bands, volume discounts, shipping brackets, late-payment penalties, utility tariffs, bonus accelerators — all the same shape, and all sharing the same two questions. Which band is this in, and does the rate apply to everything or only to the part above the line?

What this covers. VLOOKUP, MATCH, INDEX, IF, SUMPRODUCT, SUMIFS and ROUND work in every version this century, and everything essential here is built from them. XLOOKUP, XMATCH, IFS, LET and SWITCH need Microsoft 365 or Excel 2021; where one appears, the older equivalent sits beside it.

1) One Rate Card, Ten Reps, Two Answers

The plan is four lines long. It says: reps earn 4% from 50,000, 6% from 100,000, 8% from 150,000 and 10% from 250,000, measured on quarterly sales.

And here is what that produced, next to what it can also be read to mean:

Look down the last column before you read anything else. The differences are 2,000.00, 4,000.00, 7,000.00 and 12,000.00, and they repeat. Aisha sold 248,900 and Yuki sold 151,200 — 97,700 apart — and the two readings disagree by exactly 7,000.00 for both of them. Marcus sold 52,400 and Priya sold 99,880, and both differ by exactly 2,000.00.

That is the first useful thing about this whole subject: the gap between the two readings is not proportional to sales. It is a lump sum attached to the band, and section 3 shows why that lump sum is the entire problem.

🎯 Scenario: Before touching a formula, put both totals on screen and label them. =SUM(E2:E11) is 99,398.40 — what went out. Section 7 gives you a single cell for the 50,398.40. A commission run with one number on it cannot be argued about, only believed.

2) The Sentence That Costs 49,000.00

"4% from 50,000" is genuinely ambiguous, and both readings have names.

Whole-amount (also called cliff, retroactive, or rate applies to all): find the band, apply its rate to every unit of sales. Tomas at 100,120 is in the 6% band, so 100,120 × 6% = 6,007.20.

Marginal (also called progressive, incremental, differential, or sliding scale): each rate applies only to the slice of sales inside its own band. Tomas earns nothing on his first 50,000, 4% on the 50,000 that follows, and 6% on the 120 above 100,000: 0 + 2,000.00 + 7.20 = 2,007.20.

Which one a plan means is not a matter of taste; it is a matter of what is written, and different domains default differently:

The reason the myth "a raise pushed me into a higher tax bracket and I take home less" survives is that people apply the whole-amount reading to a marginal system. It cannot happen under a marginal ladder — and, as section 3 shows, it happens constantly under a whole-amount one.

🎯 Scenario: Settle the reading in one sentence, in writing, before you build anything: "the 6% applies to the whole of sales once sales reach 100,000" or "the 6% applies only to sales above 100,000." Then build the formula that says exactly that. Half of the wrong commission runs in the world are correctly-built implementations of the sentence nobody wrote down.

3) The Cliff, Priced

Under the whole-amount reading, crossing a threshold pays a bonus that has nothing to do with the sale that crossed it. Here is what each threshold is worth at the moment it is crossed:

Those steps are the differences between the lump sums in section 1 — 0 → 2,000 → 4,000 → 7,000 → 12,000 — which is what "the gap is attached to the band" means in practice.

Now put the reps against them:

• Kwame Osei sold 49,750 and earned nothing. He is 250 short of the first threshold. That 250 of sales is worth 2,000.00 of commission — a return of 800% on the last order of the quarter, and 0% on every order before it.
• Priya Nair and Tomas Lindqvist are 240 apart in sales and 2,012.00 apart in pay. Priya sold 99,880 and took 3,995.20; Tomas sold 100,120 and took 6,007.20. Under the marginal reading the same 240 of sales is worth 12.00.
• Daniel Fischer and Yuki Tanaka are 2,300 apart and 3,162.00 apart in pay. Marginally, that 2,300 is worth 162.00.
• Sofia Marchetti crossed 250,000. At 249,999.99 the plan pays 20,000.00. At 250,000.00 it pays 25,000.00. One cent of sales is worth 5,000.00 — and to earn that same 5,000.00 at the 8% rate a rep would have to sell another 62,500.

Then look again at the sales column. Three of ten reps sit within 120 of the 100,000 threshold: 99,880, exactly 100,000, and 100,120. That clustering is not a coincidence and it is not luck. Bunching just above a threshold is the signature of a cliff plan — it is what a sales force does when the last 120 of a quarter is worth 2,000.00 and the first 99,880 is worth 4% — and the same behaviour has a mirror image nobody puts in a report: the rep at 249,000 in late September who cannot reach 250,000 has 5,000.00 of reasons to let the deal close in October instead.

🎯 Scenario: Sort the sales column and look at the distribution around each threshold. A histogram with a spike immediately above a line and a hole immediately below it is a plan changing behaviour, not a market. It is also the cheapest audit in this article — one sort, no formulas.

4) How Excel Finds a Band

Whichever reading you implement, something has to answer "which band is 148,900 in?" That is an approximate match, and it is the one place where Excel's most-used lookup functions do their most useful work.

Put the rate card in H2:I6, thresholds ascending, one row per band, and the first row must be the lowest possible value — usually 0:

Four ways to read a rate out of it, all returning 6% for Daniel's 148,900:

=VLOOKUP(C9,$H$2:$I$6,2,TRUE)              the fourth argument is the whole point
=XLOOKUP(C9,$H$2:$H$6,$I$2:$I$6,,-1)       match_mode -1: exact, or the next smaller
=INDEX($I$2:$I$6,MATCH(C9,$H$2:$H$6,1))    match_type 1: largest value ≤ lookup
=LOOKUP(C9,$H$2:$H$6,$I$2:$I$6)            the oldest form; always approximate

Every one of them means the same thing: find the largest threshold that is not greater than the value, and take its row. That is precisely the definition of a band, which is why a rate card should never be written as a chain of IFs when a two-column table will do.

Three rules the table has to obey, and one trap that follows from them:

1. Thresholds ascending. VLOOKUP(...,TRUE) and MATCH(...,1) binary-search the column. On a descending or unsorted table they land on a row that has nothing to do with the answer, return the rate they find there, and report no error at all. XLOOKUP's -1 scans linearly by default and so is more forgiving — but set search_mode to 2 or -2 for speed on a long table and the requirement is back. Sort it and the question never arises.
2. A row for the bottom. Delete the 0 row and Kwame's 49,750 is below everything in the column: #N/A, from all four formulas. Then somebody wraps it in IFERROR(...,0), which is right for this plan and wrong the moment a plan has a floor rate.
3. Numbers, not text. A threshold typed as 50,000 with the comma, or imported as text, is not a number, and an approximate match against a mixed column compares apples to a string. It does not error either.

The trap: VLOOKUP's fourth argument defaults to TRUE and XLOOKUP's match_mode defaults to exact. They are opposite defaults, which means a band lookup written as =XLOOKUP(C9,$H$2:$H$6,$I$2:$I$6) returns #N/A for every rep who is not sitting exactly on a threshold — nine of the ten here — while the same omission in VLOOKUP silently gives you a product lookup that guesses. One fails loudly on the right rows and one fails quietly on the wrong ones.

🎯 Scenario: Add a fifth column beside the run holding =VLOOKUP(C2,$H$2:$I$6,2,TRUE) and compare it against the rate that was actually paid in D2:D11. Ten matches means the bands were applied correctly, which is a different question from whether the bands were applied to the right base — and settling it first stops the argument in section 6 from being about two things at once.

5) The Ladder of IFs, and the Order It Has to Be Written In

Most rate cards on most spreadsheets are not tables; they are a nested IF written by somebody in a hurry. It works, and it has one failure mode that is worth more than the rest of this section combined:

=IF(C2>=250000,0.1,IF(C2>=150000,0.08,IF(C2>=100000,0.06,IF(C2>=50000,0.04,0))))

That is correct, and it is correct because it is written from the top down. IF stops at the first test that is true, so the largest threshold has to be tested first. Write the same four bands in the order they appear on the rate card:

=IF(C2>=50000,0.04,IF(C2>=100000,0.06,IF(C2>=150000,0.08,IF(C2>=250000,0.1,0))))

and every rep above 50,000 gets 4%, because the first test is true for all of them and the other three are never reached. Nine of these ten reps would be paid 4%: the run would total 53,609.60 instead of 99,398.40, with no error, no #N/A, and a formula that reads perfectly well out loud. IFS has the same property and hides it better, because its arguments look like a table:

=IFS(C2>=250000,0.1,C2>=150000,0.08,C2>=100000,0.06,C2>=50000,0.04,TRUE,0)

Still first-true-wins. Still has to be descending. The final TRUE,0 is the catch-all; leave it out and anyone below 50,000 gets #N/A.

Then there is the boundary. Rosa Alvarez sold exactly 100,000, which is the row every ladder should be tested against and almost none are. With >= she is in the 6% band and paid 6,000.00. Change one operator to > and she drops to 4% and 4,000.00 — a 2,000.00 error affecting exactly one row in ten, on a formula that returns a sensible number for everybody else.

Two things worth knowing about that:

• The plan's own words settle it. "4% from 50,000" and "4% on sales of 50,000 or more" are >=. "4% on sales over 50,000" is >. If the plan says "over", the rate card's own thresholds are wrong by one unit, not the formula.
• The marginal formulas in sections 6 and 7 are immune to this. At exactly 100,000 the slice above 100,000 is zero, so it contributes nothing whichever operator you use. The boundary bug is a whole-amount bug.

🎯 Scenario: Whatever ladder you inherit, test it on the four threshold values themselves — 50,000, 100,000, 150,000, 250,000 — and on those values minus 0.01. Eight cells, thirty seconds, and it catches the operator, the ordering and the missing catch-all in one pass. Nothing about a rep's real sales figure tests any of them.

6) The Marginal Version: Base, Threshold, Rate

The marginal reading is where people reach for a formula that adds up slices, and it always turns into an unreadable nest. There is a standard way to do it, and it is the way every tax authority publishes its own tables: give each band a base — the total earned by everyone who has climbed all the way through the bands below it.

Add column J to the rate card:

Do not type that column. Build it, in J3, copied down:

=J2+(H3-H2)*I2

Each base is the one below it plus the full width of the band below it at the band below's rate: 0 + 50,000 × 4% = 2,000, then 2,000 + 50,000 × 6% = 5,000, then 5,000 + 100,000 × 8% = 13,000. A base column typed by hand is correct on the day it is typed and silently wrong the first time somebody changes a rate — which is precisely the day everybody is looking at the totals and nobody is looking at column J.

Then commission is one line, in F2:

=VLOOKUP(C2,$H$2:$J$6,3,TRUE) + (C2-VLOOKUP(C2,$H$2:$J$6,1,TRUE)) * VLOOKUP(C2,$H$2:$J$6,2,TRUE)

base of the band, plus the part of sales above that band's threshold, at that band's rate. For Aisha's 248,900: 5,000.00 + (248,900 − 150,000) × 8% = 5,000.00 + 7,912.00 = 12,912.00. For Sofia's 262,540: 13,000.00 + 12,540 × 10% = 14,254.00.

The VLOOKUP(...,1,TRUE) in the middle looks strange — it looks the threshold up in its own column — and it is the trick that makes the whole thing one formula: it returns the threshold of the band the value landed in, so you never have to know which row that was. In modern Excel the same thing reads better, because LET lets you find the row once:

=LET(r, XMATCH(C2,$H$2:$H$6,-1),
     INDEX($J$2:$J$6,r) + (C2-INDEX($H$2:$H$6,r)) * INDEX($I$2:$I$6,r))

One XMATCH instead of three lookups: faster on a long run, and it says what it means. The -1 is XMATCH's "exact or next smaller", the same idea as VLOOKUP's TRUE.

🎯 Scenario: Copy F2 down and total it. If it comes to 50,398.40 the rate card is wired correctly. Then change the 8% in I5 to 9% and watch the base column move by itself — 13,000.00 becomes 14,000.00, and Sofia's commission with it. That self-updating column is the entire reason to keep the rate card as data rather than as text inside a formula.

7) One Cell, No Base Column: SUMPRODUCT

There is a version with no helper column at all, and it is the one to know if you ever have to put a marginal calculation inside somebody else's model. Add a differential rate column — how much each band's rate adds to the one below — in K2, copied down:

=I2-I1

Then the whole marginal calculation is:

=SUMPRODUCT((C2>$H$2:$H$6)*(C2-$H$2:$H$6)*$K$2:$K$6)

For Tomas at 100,120: (100,120 − 0) × 0% + (100,120 − 50,000) × 4% + (100,120 − 100,000) × 2% = 0 + 2,004.80 + 2.40 = 2,007.20. The two bands above him are switched off by the (C2>$H$2:$H$6) comparison, which returns TRUE/FALSE and is turned into 1s and 0s by the multiplication.

Why it works is worth ten seconds: charging every threshold's extra rate on everything above that threshold gives the same answer as charging each band's full rate on its own slice. It is the standard rewrite of a piecewise function, and it is why tax tables and this formula agree to the cent.

The gate is not optional. Drop the (C2>$H$2:$H$6) and the bands above the rep contribute negative numbers, because C2 − threshold is negative there. Tomas comes out at −1,988.00 — not an error, not a zero, just a confidently wrong number with a minus sign that somebody will read as a clawback.

And unlike the ladders in section 5, this formula does not care about > versus >=: at exactly 100,000 the slice above 100,000 is zero either way, which is why Rosa's row is the same 2,000.00 under both.

🎯 Scenario: Build both — section 6's base-column version and this one — in two columns beside each other, and subtract. They must agree to zero on all ten rows. Two formulas that were derived differently and agree exactly are the closest thing a spreadsheet offers to a proof; two that agree because one was copied from the other prove nothing at all.

8) The Total Is Not the Sum of the Parts

This is the error that survives every review, because the formula is right and the range is wrong.

The four regions sold this:

The right-hand column is what you get by doing the obvious thing: =SUMIFS($C$2:$C$11,$B$2:$B$11,"North") for the region's sales, then the section 7 formula on that total. It overstates the commission by 41,079.60, and running the ladder on the company's 1,389,990 as a single figure is worse still: 126,999.00, two and a half times the truth.

A tiered rate is not additive. The ladder is defined on one rep's quarter, so it can only ever be applied to one rep's quarter. The correct order is: commission per rep first, then add up whatever you like.

=SUMIFS($F$2:$F$11,$B$2:$B$11,"North")        adds the commissions      → 20,015.20
=SUMPRODUCT(--($B$2:$B$11="North"),$F$2:$F$11) same, older syntax

The same trap has a time axis, and it is the one that gets into real plans. Pay this ladder monthly and a rep who sells 46,000 in each of three months earns nothing at all — every month is below the first threshold. The same 138,000 measured quarterly earns 4,280.00 marginally, or 8,280.00 under the whole-amount reading. None of the three is wrong; they are answers to different questions. The plan has to name the period the ladder is measured over, and a spreadsheet that adds up three monthly runs is not calculating the quarterly one.

🎯 Scenario: Put two labelled cells at the top of the sheet: =SUM(F2:F11), the commissions added up, and the same rate-card formula pointed at =SUM(C2:C11), the ladder run on total sales. Here they read 50,398.40 and 126,999.00. Once those two numbers are on screen together, nobody in the room applies a rate card to a subtotal again.

9) Rounding, and the Rate Column That Lies

Two small things that turn into reconciliation meetings.

Round once, at the row. Commission is money, so it should be rounded to the cent where it is calculated, not left at fifteen decimal places to be rounded by the number format:

=ROUND(<the marginal formula>,2)

If you round only the total, the total will disagree with the sum of what each rep sees on their statement — never by much, always by enough to cost an afternoon. If you round at the row, the total is the sum of the numbers people were actually paid, which is what a payroll file has to reconcile to.

The rate column shows 8% and holds whatever it holds. D2:D11 in this run is formatted as a percentage with no decimals. A rate of 0.0825 displays as 8% in that format, and 0.0825 × 176,300 is 14,544.75, not 14,104.00. Percent formatting with zero decimals is the single most effective way to hide a wrong rate in plain sight, because the column looks exactly like the rate card. Format rate columns with one or two decimals in any sheet that pays people, and check a rate by clicking the cell, not by reading the column.

And its louder cousin: a rate entered as 4 rather than 0.04 or 4%. Tomas's 100,120 at "4" is 600,720.00 — an error so large it gets caught in the same minute, which makes it the least dangerous mistake in this article.

🎯 Scenario: Widen D2:D11 to four decimal places for ten seconds. Every rate that is not exactly what the card says will announce itself, and you can put the format back.

10) Checking a Run Somebody Else Built

You do not need to rebuild a commission run to audit it. You need one column and two cells.

Recompute each rep's commission from the rate card with the section 7 formula in G2, then flag the differences:

=IF(ROUND(E2-G2,2)<>0,"CHECK","")

The ROUND(...,2) matters: comparing two floating-point results with <> will flag rows that differ by 0.0000000001, and a column of "CHECK" against ten identical numbers teaches everybody to ignore the column. Then one cell for the run:

=SUM(E2:E11)-SUM(G2:G11)

which is 49,000.00 here — 97.2% of what the run should have paid, sitting in one cell, in a workbook where every individual formula was correct. Expressed against sales, the run paid 7.15% of 1,389,990 where the plan describes 3.63%. Those two percentages are the version of this that a finance director will act on, because a commission plan is budgeted as a percentage of revenue and this one is running at nearly twice its budget.

🎯 Scenario: Whenever you inherit a calculated column, rebuild it once in the next column over and subtract. If the difference is zero, delete your column and trust the sheet. If it is not, you have found either a bug or a rule nobody told you about, and both are worth the five minutes.

11) Twelve Traps

1. The band table is not sorted ascending. VLOOKUP(...,TRUE) and MATCH(...,1) binary-search it and return a rate from a row they had no business landing on. No error, ever.
2. No bottom row. Without a 0 threshold, everyone below the first band gets #N/A — and the IFERROR(...,0) that follows becomes wrong the day the plan gets a floor rate.
3. XLOOKUP without -1. Its default is an exact match, the opposite of VLOOKUP's default, so a band lookup returns #N/A for everyone not sitting exactly on a threshold.
4. VLOOKUP without FALSE somewhere else in the sheet. The same default that makes band lookups convenient makes product lookups guess. Both are in one workbook; only one of them wants TRUE.
5. The ladder of IFs written ascending. First-true-wins means everybody above the lowest threshold gets the lowest rate. Here that pays 55,209.60 instead of 99,398.40, and reads correctly out loud.
6. > where the plan says "from". Rosa sold exactly 100,000. One operator moves her 2,000.00, and no other row in the file can detect it.
7. A rate card applied to a subtotal. Region totals through this ladder give 91,478.00 against a true 50,398.40; the company total gives 126,999.00. The formula is right and the range is wrong.
8. A base column typed by hand. It is correct until a rate changes, then it is silently stale. Build it with =J2+(H3-H2)*I2.
9. SUMPRODUCT without the (C2>thresholds) gate. Bands above the value contribute negative amounts; Tomas comes out at −1,988.00 rather than 2,007.20.
10. Thresholds stored as text. 50,000 typed with a comma, or imported from a system, compares as text against a number and matches nothing sensibly.
11. Percent formatting with no decimals. 0.0825 and 0.08 both display as 8%, and only one of them is on the rate card.
12. Negative sales and credit notes. A returned order can drop a rep below a threshold after they have been paid at the higher one; under a whole-amount plan that clawback is the size of the lump sum, not the size of the return. Decide in the plan whether bands are recalculated year to date or settled each quarter, because the spreadsheet will happily do either.

12) The Checklist

• The plan says, in writing, whether rates apply to all sales or only to the part above each threshold
• The rate card is a table in cells, not numbers typed inside a formula
• Thresholds sorted ascending, one row per band, a row for the bottom of the scale
• Thresholds and rates are numbers, and the rate column is formatted with decimals
• Every approximate match states its mode explicitly: TRUE, -1, or 1
• Any IF/IFS ladder is written from the highest band down, and ends in a catch-all
• The formula has been tested on each threshold value exactly, and on each minus 0.01
• Any base or differential column is calculated, not typed
• Commission is calculated per person per period, and only then summed
• Rounded to the cent at the row, not at the total
• Somebody has looked at the distribution of sales around each threshold

Exercises

Use the grid above, and the rate card 0/50,000/100,000/150,000/250,000 at 0/4/6/8/10%.

1. Both readings. Build the whole-amount commission and the marginal commission in two columns and total each. You should get 99,398.40 and 50,398.40. Then write the one-cell formula for the difference.
2. Find the band. Write the rate lookup four ways — VLOOKUP, XLOOKUP, INDEX/MATCH and LOOKUP — and confirm all four return 6% for 148,900. Then sort the rate card descending and report which of the four still gives the right answer.
3. Break it deliberately. Delete the 0 row from the rate card. Which rep breaks, what does he return, and what is the smallest change that fixes it without an IFERROR?
4. The boundary. Compute Rosa's commission both ways with >= and with >. State the difference under each reading, and say why one of the two is unaffected.
5. No helper column. Write the marginal commission as a single SUMPRODUCT, then delete the (C2>...) comparison and record what each of the ten reps returns. How many of the ten go negative?
6. The aggregation trap. Total sales by region with SUMIFS, run the ladder on each regional total, and compare with the sum of the individual commissions. Then say in one sentence why the second is the only defensible number.

Summary

A tiered rate asks two questions, and a spreadsheet will only ever answer the second one. Which band is this in is a lookup — approximate match, ascending thresholds, a row for the bottom, and the mode written down explicitly rather than left to a default that differs between VLOOKUP and XLOOKUP. What does the rate apply to is not a spreadsheet question at all. It is a sentence in a plan, and until somebody writes it down, both answers are one formula away and neither will ever produce an error.

So: settle the reading first, in words. Keep the rate card as data, with the base or differential column calculated rather than typed. Test on the thresholds themselves, where every off-by-one lives. Apply the ladder to one person and one period, then sum — never the other way round. And when you inherit a run, rebuild one column beside it and subtract, because the difference is a single number and a single number is the only thing that gets a payment stopped.

The alternative is this quarter: ten correct rates, ten correct multiplications, 99,398.40 out of the door, and 50,398.40 in the plan that authorised it.