About this exercise
A 45-row dataset on one sheet, one answer cell, about 8 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- E2
=D2*IF(D2>=$G$5,$H$5,IF(D2>=$G$4,$H$4,IF(D2>=$G$3,$H$3,$H$2)))
A nested IF is a chain of questions: is the figure at least the top threshold? If yes, use the top rate; if not, ask the same about the next tier, and so on, with the lowest rate as the final else. Testing from the top down matters because a $236,400 rep also satisfies the lower tiers — the first test that passes decides. The thresholds and rates come from G2:H5 with absolute references, so one formula filled down prices all 45 reps and still works after finance edits the table.
Pitfall 1: testing from the bottom up with >= (IF(D2>=$G$2,$H$2,…)) always stops at the first tier, because every rep clears $0 — reverse the order or use < from the bottom. Pitfall 2: > instead of >= pays 4% to the rep with exactly $100,000.00; tier plans are written "from", so the boundary belongs to the higher tier. Pitfall 3: typing the thresholds and rates into the formula (IF(D2>=200000,8%,…)) breaks the moment the table changes; the grader edits G5 and H5 and expects the commission to follow. Alternatives: IFS(D2>=$G$5,$H$5,D2>=$G$4,$H$4,D2>=$G$3,$H$3,TRUE,$H$2) is the readable form in Excel 2019 and later; VLOOKUP(D2,$G$2:$H$5,2,TRUE) and XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,,-1) (365) scale to twenty tiers without nesting, but need the thresholds sorted ascending. Beyond four tiers a lookup is the maintainable choice; nested IF beyond three levels is where audit findings start.
Other ways that work
- =D2*IFS(D2>=$G$5,$H$5,D2>=$G$4,$H$4,D2>=$G$3,$H$3,TRUE,$H$2) — IFS, Excel 2019 and later
- =D2*VLOOKUP(D2,$G$2:$H$5,2,TRUE) — approximate match on the ascending thresholds
- =D2*XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,,-1) — Excel 365; match mode -1 picks the next smaller threshold
- =D2*IF(D2<$G$3,$H$2,IF(D2<$G$4,$H$3,IF(D2<$G$5,$H$4,$H$5))) — nested IF built from the bottom with <
Common mistakes
Filling the formula down with relative references into the tier table (IF(D2>=G5,H5,…)): E3 then compares against G6 and H6, which are empty, so from row 3 on the commission is wrong or zero. Lock the table as $G$5, $H$5 and so on.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.