Absolute reference · البرمجة مع مارتن

A relative address moves

=B2-C2 dragged down → =B3-C3. That is what we wanted with pocket money.

Sometimes you need always the same cell — 10 % off in F1 for the whole column. Without a dollar F1 shifts to F2, F3…

Absolute reference: $F$1 — column F and row 1 locked.

💡

In Sheets/Excel: click in the formula and press F4 — it switches $F$1, F$1, $F1, F1. $ before the letter = column fixed. $ before the number = row fixed.

A discount in one cell

Sheet ملاحظةs. In F1 type 0.9 (90 % of the price).
G1 100, H1 formula =G1*$F$1. Enter. 90.
G2 50. نسخ H1 to H2 (Ctrl+C, H2, Ctrl+V). H2 must be =G2*$F$1, result 45. F1 did not move.
If H2 were =G2*F2, the discount vanished — F2 is empty.

Only a column or only a row

$A1 — column A fixed, the row runs (less common)
A$1 — row 1 fixed, the column runs. For example multiply a whole column by a constant from row 1.

⚠️

Too many dollars. =$B$2-$C$2 dragged down still counts the same row 2. On pocket money daily difference do not put dollars on B2 and C2 — you want a relative shift.

Tax or a rate on pocket money

Pocket E1 10 — “reserve Kč”.
F2 =B2-$E$1 — Monday minus the reserve (practice, not real life). Drag F2:F8.
Each row takes B a different day, but E1 still 10.

🏫 Grade average

One sheet, two columns: subject and grade. At the bottom a sum and an average.

💰 Pocket money

Rows = days, columns = income / spend / left.

📊 Class survey

One question, answers in one column, later a chart.

🏆 Mini dashboard

Three numbers at the top and one chart. Numbers as a link, not copied by hand.

Now you 💪

You wrote a formula with $F$1 or $E$1 and copied it down.
After copy the constant cell stayed the same in the formula bar.
F4 can add/remove dollar(s) in a formula.
You know when a dollar not — daily B2-C2 without dollars.
A change in E1 or F1 recalculates the whole column.

✅

Check: An absolute reference holds one cell. Relative shifts rows.

What to take from this lesson

$ locks a column, a row, or both.
A constant (discount, rate) belongs in $E$1 and in the formula $E$1.
Pocket money daily = relative B and C.
F4 switches the reference type.
Wrong dollars = everywhere the same row 2.