About this exercise
A 12-row dataset on one sheet, one answer cell, about 7 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- B4
=$B$1*$A4*(1-B$3)
A mixed reference locks one half of the address. $A4 keeps the column fixed at A while the row number follows the copy, so every cell reads the quantity of its own row; B$3 keeps the row fixed at 3 while the column letter follows the copy, so every cell reads the discount above it. $B$1 is fully locked because the list price is the same for all 80 cells. One formula therefore produces the whole grid, and changing a quantity, a discount or the list price updates exactly the cells that depend on it.
Pitfall 1: the most frequent error is locking the wrong half — A$4 and $B3 produce a grid whose first row and first column look right and everything else is wrong, which is hard to spot without the robustness check. Pitfall 2: with relative references only (=B1*A4*(1-B3)) the formula works in B4 and breaks everywhere else; with everything absolute it repeats one number 80 times. Shortcut: select the whole grid first, type the formula for the top-left cell and press Ctrl+Enter (⌘+Return) (in Excel; in this practice grid Ctrl/⌘+Enter runs Check) — it fills the selection and keeps relative/mixed parts consistent. Alternatives: in Excel 365 a single array formula =$B$1*A4:A13*(1-B3:I3) in B4 spills all 80 values (two-dimensional broadcasting); =$B$1*$A4-$B$1*$A4*B$3 is algebraically the same as the (1-discount) form but recomputes the gross twice. Modeling note: price grids like this are the standard "two-way data table" pattern; the same reference discipline drives loan tables (rate × term) and sensitivity analyses.
Other ways that work
- =$A4*$B$1*(1-B$3) — same formula, factors reordered
- =$B$1*A4:A13*(1-B3:I3) in B4 — Excel 365 only; one spilling array formula for the whole grid
- =ROUND($B$1*$A4*(1-B$3),2) — rounded to cents; accepted within the grader's half-cent tolerance
Common mistakes
Writing =$B$1*A4*(1-B3) (quantity and discount left relative) and filling across: in C4 the quantity reference slides to B4 — the cell you just filled — so the row turns into a chain of ever-growing numbers.
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 15, 2026. Author: GridBelt team.