A price grid with mixed references
PremiumE2:G4 should show what each item costs for 10, 50 and 100 units. Every cell multiplies a price from column B by a number of units from row 1.
Click Continue when you're ready.
Already know it? Type the formula straight into E2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Price | Units | 10 | 50 | 100 | ||
| 2 | Pens | 4 | ||||||
| 3 | Notebooks | 12 | ||||||
| 4 | Folders | 7 | ||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
Premium lesson
Unlock Relative and absolute references and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
The formula has to move in two directions. Filled right, the price must stay in column B, so it is $B2: the column is locked and the row moves. Filled down, the units must stay in row 1, so they are E$1: the row is locked and the column moves. One formula then fills the whole grid. Ten pens at 4 each cost 40.
Common mistake: Locking both parts with $B$2 or $E$1. The grid fills, but every row or every column shows the same number.
The answer
- E2
=$B2*E$1