Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. Absolute references

Mixed reference practice: build a quantity × discount price grid with one formula

  • Beginner
  • ~7 min
  • Absolute references

A distributor sells a monitor arm at a list price of $249 (cell B1) and quotes volume discounts from 0% to 40%. Sales wants a ready-reckoner: order totals for 10 quantities (column A) across 8 discount levels (row 3). Write one formula in B4 that works in all 80 cells of the grid when filled right and down — this is what mixed references ($A4 and B$3) are for.

What you need to do

  1. 1Click B4 and build the formula from three parts: the list price (B1), the quantity on the same row (A4) and the discount at the top of the same column (B3).
  2. 2Lock each part for copying: B1 fully ($B$1), the quantity by column only ($A4) and the discount by row only (B$3). F4 / ⌘T cycles through the four $ variants of a reference.
  3. 3Press Enter, then fill B4 right to I4 with the fill handle, then select B4:I13 and press Ctrl+D / ⌘D to fill the row down to row 13.
  4. 4Spot-check: I13 must read =$B$1*$A13*(1-I$3). Then change B1 and watch all 80 totals move.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedFill B4:I13 with order totals (list price in B1 × quantity in column A × (1 − discount in row 3)) using one formula with mixed references that you copy across the whole grid.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

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.

In these programs

  • Excel for Data AnalystsDay 1
  • Excel for Sales and MarketingDay 1
  • Excel for Accounting and BookkeepingDay 1

Related exercises

  • Apply one tax rate to every line ($ reference)Beginner, ~5 min
  • Row totals with the fill handleBeginner, ~4 min
  • Share of total per region, formatted as %Beginner, ~5 min
  • Total revenue with SUMBeginner, ~3 min

Function reference

Official documentation

Next exercise

Share of total per region, formatted as %