I85PREP
⌘ K
MO-210 study materials
MO-210 study resources

The Commission Model That Mistrusts Its Own Boundaries

MO-210 · Put your knowledge to work in context.

All case studies

The Commission Model That Mistrusts Its Own Boundaries

Sales ops builds a commission model. Sheet Data holds a table with columns Rep, Region, Sales and Target. Sheet Rates holds a band table with MinSales and Rate; the published rate card says a rep earns the tier whose MinSales is at or below their Sales. The current formula is =VLOOKUP([@Sales],Rates!$A$2:$B$6,2,TRUE). Two complaints arrive: a rep at exactly 250,000 is paid 4% while the card shows 5% for "250,000 and above", and reps in Northwest appear in the West regional total, which uses =SUMIFS(Data[Sales],Data[Region],"West"). Separately, the bonus column =IF([@Sales]>[@Target],8%,0) pays nothing to reps exactly on target, and attainment =IFERROR([@Sales]/[@Target],0) shows 0% for new hires with a blank Target, dragging the regional average down.

1. Is the 4%-instead-of-5% result a bug in VLOOKUP or a data/wording problem? Give the diagnostic order and a sort-independent alternative.

Read the worked answer

Diagnostic order - cheapest first.

  1. Sort order. TRUE (approximate) match requires the first column of the Rates range to be sorted ascending; otherwise the result is unpredictable. Check with a helper column =A3>A2 copied down - any FALSE means unsorted.
  2. Text thresholds. If MaxSales-like values were pasted with a trailing space or apostrophe, they are text and the binary search misplaces them. =COUNT(Rates[MinSales]) must equal the row count.
  3. Semantics. With TRUE, VLOOKUP returns the largest threshold less than or equal to the lookup value. At 250,000 that is the 250,000 band - so this formula should pay 5% if the card reads "250,000 and above". If the card actually means strictly above 250,000, the thresholds themselves are wrong and must be stated as the first value of each band (250,001), not the last value of the previous one.

Sort-independent alternative. Find the threshold, then look it up: =INDEX(Rates[Rate],MATCH(MAXIFS(Rates[MinSales],Rates[MinSales],"<="&[@Sales]),Rates[MinSales],0)) This works regardless of row order. XLOOKUP([@Sales],Rates[MinSales],Rates[Rate],,-1) is cleaner but still expects ascending data.

Verification. Build a small test grid of boundary values (249,999 / 250,000 / 250,001) and confirm each returns the rate the card promises.

2. Why does "*West*" pull in Northwest, and what is the correct criteria strategy if some regions are named "West" and others "West Coast"?

Read the worked answer

Why. In SUMIFS, COUNTIF, SUMIF and AVERAGEIF, * and ? are wildcards inside text criteria, and *West* matches any cell containing west - West, Northwest, West Coast, Midwest. The wildcards are part of the criteria language, not a setting you switch off per formula.

Strategy.

  • If the region list is exact, use "West". Exact criteria never over-match.
  • If you must match West and West Coast but exclude Northwest, match the left edge: "West*" matches West and West Coast but not Northwest, because Northwest does not begin with West.
  • If both edges are genuinely needed, wrap it in SUMPRODUCT with a text function: =SUMPRODUCT((LEFT(Data[Region],4)="West")*Data[Sales]).
  • To match a literal asterisk (a data value containing *), escape it as ~*.
  • When the criteria live in a cell, concatenate: "*"&$B$2&"*", and remember a cell containing ? or * will still be interpreted as wildcards.

Check. Compare the regional total against a pivot table of Sales by Region; any difference is over-matching.

3. Fix the at-target bonus and explain why IFERROR(...,0) on attainment is the wrong tool here.

Read the worked answer

Bonus. The rule is "at or above target", so the operator must be >=, not >. Use =IF([@Sales]>=[@Target],8%,0). Test the boundary explicitly: a row with Sales = Target = 100,000 must return 8%.

Why IFERROR is wrong.

  • [@Target] blank is treated as 0, so the division raises #DIV/0!, and IFERROR converts "target not yet set" into "0% attainment". That is a fabricated number, not a missing one, and AVERAGE includes it.
  • IFERROR also swallows #REF! and #VALUE! - genuine data errors you need to see.

Correct pattern. =IF([@Target]="","",[@Sales]/[@Target]). The empty string keeps the row readable, and AVERAGE ignores it (a text result is not counted). If you prefer a number-free blank, return NA() and let the chart break the line, or compute the regional average with =AVERAGEIFS(Data[Attainment],Data[Target],">0").

Report the exclusion. State how many reps are excluded for a missing target, so nobody assumes the average covers everyone.