Mixed references for a multiplication grid
BeginnerA mixed reference locks either the row or the column, but not both. This makes it possible to write one formula and copy it in two directions at once. Pricing matrices, commission tables, and discount grids all rely on this technique.
There are two kinds of mixed references:
- $X2: the
$before the column letter locks the column. This means copying right keeps the column fixed, while copying down lets the row number adjust normally - Y$1: the
$before the row number locks the row, so copying down keeps it fixed while the column letter adjusts as you copy right
In this sheet, you're building a pricing matrix. Row 1 (cells B1 to F1) holds quantity tiers: 10, 25, 50, 100, and 250 units. Column A (cells A2 to A6) holds unit prices in dollars. Each cell in the 5x5 grid (B2 to F6) should show the total cost for that combination of unit price and quantity.
What you need to do
- Click cell B2.
- Write a formula that multiplies the unit price (in column A) by the quantity (in row 1). Think about which
$lock you need on each reference so that copying across and copying down both adjust correctly. - Press Enter.
- Select B2 again, then copy the formula across to F2 by dragging the small square in the corner of the cell (called the fill handle) to the right.
- Select B2:F2, then copy those cells down through row 6 (drag the fill handle down).
When you are done, every cell in B2:F6 should show the total cost for its unit price and quantity combination.
Need some help?
Hint 1
You need two mixed references in your formula: one that always points to column A (the unit price) and one that always points to row 1 (the quantity). Together, they let a single formula correctly calculate total cost in every cell of the grid.
Hint 2
Think about what happens when you copy the formula to the right. The unit price should not change; it should always come from column A. A dollar sign before the column letter locks that column in place, no matter how far right you copy.
Hint 3
Now think about copying downward. The quantity should always come from row 1, so it must not shift down. A dollar sign before the row number locks that row in place. Put these two ideas together: your formula multiplies a reference with a locked column by a reference with a locked row.
Mixed references for a multiplication grid
BeginnerA mixed reference locks either the row or the column, but not both. This makes it possible to write one formula and copy it in two directions at once. Pricing matrices, commission tables, and discount grids all rely on this technique.
There are two kinds of mixed references:
- $X2: the
$before the column letter locks the column. This means copying right keeps the column fixed, while copying down lets the row number adjust normally - Y$1: the
$before the row number locks the row, so copying down keeps it fixed while the column letter adjusts as you copy right
In this sheet, you're building a pricing matrix. Row 1 (cells B1 to F1) holds quantity tiers: 10, 25, 50, 100, and 250 units. Column A (cells A2 to A6) holds unit prices in dollars. Each cell in the 5x5 grid (B2 to F6) should show the total cost for that combination of unit price and quantity.
What you need to do
- Click cell B2.
- Write a formula that multiplies the unit price (in column A) by the quantity (in row 1). Think about which
$lock you need on each reference so that copying across and copying down both adjust correctly. - Press Enter.
- Select B2 again, then copy the formula across to F2 by dragging the small square in the corner of the cell (called the fill handle) to the right.
- Select B2:F2, then copy those cells down through row 6 (drag the fill handle down).
When you are done, every cell in B2:F6 should show the total cost for its unit price and quantity combination.
Need some help?
Hint 1
You need two mixed references in your formula: one that always points to column A (the unit price) and one that always points to row 1 (the quantity). Together, they let a single formula correctly calculate total cost in every cell of the grid.
Hint 2
Think about what happens when you copy the formula to the right. The unit price should not change; it should always come from column A. A dollar sign before the column letter locks that column in place, no matter how far right you copy.
Hint 3
Now think about copying downward. The quantity should always come from row 1, so it must not shift down. A dollar sign before the row number locks that row in place. Put these two ideas together: your formula multiplies a reference with a locked column by a reference with a locked row.