Locked address $A$1 · البرمجة مع مارتن

Why a dollar in a formula

نسخing shifts B2 to B3. That is usually what you want. The grade on the next row, spending on the next day. Sometimes though you need one cell that does not move — a discount, VAT, a pocket-money limit, a reserve.

$F$1 says: stay on F1, even when you drag me down.

The dollar is not decoration and it is not money. It is a lock on the address.

Three shapes you will meet:

B2 — relative. Both row and column move. You drag down → B3. You drag right → C2.
$F$1 — absolute. Nothing moves. You drag anywhere → still $F$1.
F$1 — locked row. The column moves, row 1 holds. At the start you need this rarely.
$F1 — locked column. The row moves, column F holds. Also later.

For now the first two are enough. A bare address and an address with two dollars.

F4 writes the dollar for you

Writing $ by hand works. A key is faster.

In Sheets and Excel: click in the formula on F1 (or step onto that address with the arrow) and press F4. On a Mac often Fn+F4, because F4 by itself changes brightness. On some laptops Cmd+T in Sheets.

F4 cycles:

F1 → $F$1 (lock both)
→ F$1 (lock the row)
→ $F1 (lock the column)
→ F1 (free again)

Stop on $F$1. That is the lock you want now.

💡

When F4 does nothing, you are outside the formula. First cancel Enter, click into the formula bar, cursor on F1, only then F4. On a Mac try Fn. If still nothing, type $ by hand. It works the same.

A discount on the whole column

One number at the top drives the whole model. That is the start of the Modeller.

On Kapesne prepare a lever:

	E	F
1	Reserve	10%

Inside F1 is 0.1. Percent format shows 10%. You already know that from Chart maker.

Spending is in column C. For example C2 = 20, C3 = 15, C4 = 10.

On Kapesne in E1 write Reserve. In F1 write 0.1 and turn on percents. You must see 10%.
In G1 write Take off. In G2:
```
=C2*$F$1
```
Enter. For spending 20 you must see 2 (or 2.00 Kč, if G has currency). Ten percent of twenty is two.
Drag G down to all the days. Click G3. In the formula bar you must see =C3*$F$1. C moved. F1 stayed. That is correct.
Click G5. Still $F$1, not $F$5. If you see F5 without a dollar or $F$5, the lock failed. Fix G2 and drag again.

⚠️

When you write =C2*F1 without a dollar and drag, F1 becomes F2, F3, F4… F2 is empty. Empty times 15 is 0. Suddenly you are “taking off zero” and the model looks like the reserve does not work. The dollar is not decoration.

Break it on purpose so you see it

On Poznamky the same thing in small.

A1 = 0.1 (reserve). B2 = 20, B3 = 30, B4 = 40.
In C2 write =B2*A1 without a dollar. Drag to C3 and C4.
C2 is 2. Good. C3 is 0. C4 is 0. Click C3: =B3*A2. A2 is empty. The model is dead.
Fix C2 to =B2*$A$1. F4 on A1. Drag again. C2 = 2, C3 = 3, C4 = 4. Click C4: still $A$1.

Once you break it with your eyes. Then you understand $ better than from a definition.

What you should see in the formula bar

After copying down, open three cells in a row:

G2: =C2*$F$1
G3: =C3*$F$1
G4: =C4*$F$1

The letter C and the row number travel down. $F$1 stands. When F1 shows 10% and C2 is 20, G2 is 2. When you switch F1 to 20%, G2 jumps to 4, G3 to 3, G4 to 2 — at once, without editing G.

That is the lock test. Not “it looks somehow”. The address in the formula bar.

On a Mac, when Fn+F4 is silent: click into the formula, cursor in the middle of F1, type $ before F and before 1. A handmade dollar is the same lock. F4 is only faster.

What often goes wrong

A dollar only on the column. You write $F1. You drag down and it becomes $F2, $F3. A column lock is not enough when the lever lives in row 1. You want both dollars: $F$1.

The lever in the wrong format. In F1 there is 10 and percents. Inside 10, on screen 1000%. The formula =C2*$F$1 turns 20 into 200. Write 0.1 or 10% with the sign.

You drag the lever row too. G1 is a heading, G2 a formula. Do not start dragging from G1.

#HODNOTA! in G. C2 is text 20 Kč written by hand. Rewrite to a number, currency format.

💰 Pocket money — reserve

Change F1 from 10% to 20%. The whole column G recalculates. From twenty of spending, 4 is taken off now, not 2. One number at the top drives the model. You do not have to fix ten formulas. That is the reason for the dollar.

Now you 💪

Two versions of the same formula.

First: without a dollar copy =C2*F1 down. Open G4. The F address ran away. The numbers are zeros or nonsense.

Second: fix to =C2*$F$1. F4. Drag again. On the bottom row $F$1 still lights up. Then change F1 to 20%. The whole column G must move.

Once you can do that, you have a lever. In the next chapter you add looking up. In the third a scenario.

✅

Check: In the formula bar on the bottom row $F$1 still lights up. Changing F1 moves the whole column. Without a dollar you know how it dies.

What to take from this lesson

$ locks part of the address. $F$1 does not move when you copy.
F4 (on a Mac Fn+F4) cycles the lock. Stop on two dollars.
One cell at the top = a control for the whole column. Without a dollar you multiply by empty.