Commission tiers that land on the wrong rate
A 120-rep sales workbook stores monthly sales in D2:D121 and region in column B. A tier table on sheet 'Rates' holds lower bounds in G2:G5 (0, 25000, 50000, 100000) and rates in H2:H5 (2%, 3%, 5%, 7%). The formula =VLOOKUP(D2,Rates!$G$2:$H$5,2,TRUE) returns 7% for a rep with 24,800 of sales. Separately, a SUMIFS keyed on region returns 412,000 for 'North' when the visible North rows add to 438,500; three cells in column B contain 'North ' with a trailing space. A rep sitting at exactly 50,000 is charged 5%, while the written policy says the 7% tier starts above 50,000.
1. Why does 24,800 return 7%, and what exactly must change so the lookup is trustworthy?
Read the worked answer
TRUE (or the omitted 4th argument) means approximate match, which assumes the first column is sorted ascending. Excel searches on that assumption; with an unsorted table it stops in an arbitrary place and can return a row for a much higher threshold - exactly what 7% on 24,800 indicates.
Steps:
- Sort Rates!G2:G5 ascending: 0, 25000, 50000, 100000. Re-test 24,800 - it should fall in the 0 row and return 2% (since 24,800 < 25,000).
- Keep the absolute references
Rates!$G$2:$H$5; fill-down must not shift the table. - Consider
=XLOOKUP(D2,Rates!$G$2:$G$5,Rates!$H$2:$H$5,,-1)or=INDEX(Rates!$H$2:$H$5,MATCH(D2,Rates!$G$2:$G$5,1)), which state the match mode explicitly instead of hiding it in a boolean. - Build a test block containing 0, 24999.99, 25000, 50000, 50000.01 and 100000 so any future edit to the table is caught immediately.
2. The 50,000 boundary is being charged at 5% but policy says 7% starts above 50,000. What is the cleanest correction, and what values would you test?
Read the worked answer
The lower-bound table is inclusive: a lookup of 50,000 matches the 50,000 row, so the tier is 5%. Policy wants an exclusive upper edge, which a lower-bound table cannot express without an epsilon hack (50000.0001 - avoid it; it corrupts money and reporting).
Cleanest correction, using strict greater-than operators that mirror the policy wording:
=IFS(D2>100000,0.07,D2>50000,0.05,D2>=25000,0.03,TRUE,0.02)
Check the edges: 50,000 → 5%; 50,000.01 → 7%; 100,000 → 5%; 100,000.01 → 7%; 25,000 → 3%.
If the table must stay data-driven, redefine it as upper limits (25,000 / 50,000 / 100,000 / beyond) and use MATCH(...,-1) on a descending list, documenting in the header whether each band is inclusive of its upper bound. Either way, write the rule in the header cell - 'higher tier starts above 50,000' - because that sentence is the requirement, and the formula must be traceable to it.
3. Explain the 26,500 gap in the North total and give the fix, including why a wildcard is risky here.
Read the worked answer
SUMIFS matches criteria text exactly as stored. 'North ' with a trailing space is a different string from 'North', so those three rows are excluded - 26,500 missing from 438,500 leaves 412,000.
Fixes, best first:
- Clean the source: helper column
=TRIM(B2)and run SUMIFS against the helper; or=SUMPRODUCT((TRIM($B$2:$B$121)="North")*$D$2:$D$121). This also catches double spaces, which TRIM collapses. - At import time, trim in Power Query so the workbook never holds dirty keys.
Why "North*" is risky: it also matches 'Northern', 'North West' and 'Northampton'. Before using any wildcard, compare =COUNTIF(B:B,"North") with =COUNTIF(B:B,"North*"). If those counts differ beyond the three known rows, a wildcard will quietly over-count instead of under-counting.
After cleaning, the region total must reconcile to 438,500 and the sum of all regions to the grand total - add that check row to the sheet.