Sao chéping a formula · Lập trình với Martin

Write once. Drag.

Formula =B2-C2 on row 2. Grab the square in the corner and drag to row 3. It becomes =B3-C3.

The spreadsheet is smart: it shifts addresses by as many rows as you went down. One row down → from B2 it is B3, from C2 it is C3. You do not have to write seven formulas for seven days.

That is pocket money. Grades too, when you count points row by row. A survey too, when you turn tallies into percents.

Do a mini try on Ghi chús first, so you see the shift without worrying about korunas.

In A12 type 10. In A13 20. In A14 30.
In B12 type 1. In B13 2. In B14 3.
In C12 type =A12-B12. Enter. You will see 9.
Click C12. Drag the square in the corner to C14.
Click C13. In the formula bar there must be =A13-B13, result 18. On C14 =A14-B14, result 27.

If in C13 and C14 9 still lights up and in the formula bar still =A12-B12, you did not drag the formula — you copied a value, or in C12 you had a hard 9. Delete C12:C14, type =A12-B12 into C12 again, drag.

💡

When one cell should stay the same (for example 10 % off at the top in F1), $F$1 comes. That is Modeller. A dollar in the address says do not move. For now it is enough that references move. If you type it by mistake now, the formula will not shift when dragging and everywhere will be the same calculation from F1.

A week of pocket money

Now a real sheet. Seven days, one formula, a square.

Sheet Pocket (or a new plus → Pocket). Headings in row 1, bold: Day, In, Spent, Left.
In A2 type Mon. Fill with the square to A8 (Mon–Sun). If the days do not shift, A2 Mon, A3 Tue, select both, drag to A8.
In column B type what came in. Numbers. An example, so we have the same picture:
- B2 50 (Monday — pocket money)
- B3 0
- B4 20 (Wednesday — you found twenty in a jacket)
- B5 0
- B6 0
- B7 100 (Saturday — a birthday)
- B8 0
In column C spend. An example:
- C2 20
- C3 15
- C4 0
- C5 30 (Thursday — a snack and a drink)
- C6 10
- C7 40
- C8 0
In D2 type =B2-C2. Enter. With the example 30.
Drag the square in the corner of D2 to D8. Seven results.

What you should see in column D (with that example):

D2 = 30
D3 = −15
D4 = 20
D5 = −30
D6 = −10
D7 = 60
D8 = 0

Minus is fine. That day you spent more than came in. It is not a formula error.

Click D5. In the formula bar there must be =B5-C5, not still =B2-C2. Click D7: =B7-C7, on the screen 60.

⚠️

When you drag and everywhere is the same number (for example all 30), the formula has a hard 50 inside instead of B2. For example =50-C2. Delete column D and write cell references: =B2-C2. A hard number does not shift to another day when dragging.

If everywhere #VALUE! lights up, in column B or C there is text (fifty, 20 Kč). Turn it into number 20. Do not type a koruna into the cell — currency format comes in Chart maker.

💰 Pocket money — daily difference and a running till

What you have in D is a daily difference: came in minus spent that day. Monday does not know about Tuesday. So when you change Wednesday’s spend, Thursday (in this version) does not move.

A running balance you can do too — a till that remembers yesterday:

D2: =B2-C2 (first day, nothing to carry)
D3: =D2+B3-C3 (yesterday plus today)
Drag D3 to D8. D4 becomes =D3+B4-C4 and so on.

With our example the running till in D8 would end at 55 (170 came in, 115 spent). The daily version in D8 is 0 — that is only Sunday.

Try the running till when the daily version works. If you get lost, put D2 back to =B2-C2 and drag the daily difference again. Both versions are right. They ask a different question: how was that day? vs how much do I have now?

🏆 Mini dashboard — a seed

When you are Workbook boss, at the top on one sheet a sum, an average, a maximum and a chart light up. Today you have pieces of that:

On Grades already SUM and AVERAGE.
On Pocket seven formulas from one drag.

Under pocket money feel miễn phí to add =SUM(B2:B8) and =SUM(C2:C8) — how much came in for the week, how much went. That is not a dashboard yet. It is practice: a function + a copied formula next to each other. We will make a chart at the next level.

Second exercise: a change in the middle of the week

This is a test that the references live.

Change Wednesday’s spend C4 from 0 to 25.
With the daily version: D4 jumps from 20 to −5. D5, D6, D7, D8 stay. Wednesday does not touch Thursday.
If you have a running till: from D4 down everything moves. Thursday inherits a new yesterday.
Put C4 back to 0, or leave 25 — so you know which version runs in D.

If after changing C4 D2 (Monday) moves, the formula in D2 looks wrong. It should not reach C4. Open D2: it should be =B2-C2.

Now you 💪

Seven days. One formula at the top. A square down.

In column Left there are seven numbers, not empty and not formula text.
Click D5 (Thursday). Formula bar =B5-C5 (or with a running till =D4+B5-C5).
Change spend on Wednesday. Wednesday’s left must change.
With the daily version Thursday stays. With a running till Thursday jumps too — that is right.
Nowhere in the formula is a hard 50. There are addresses.

✅

Check: Formula kid can do equals, SUM, average and copying. References shift when dragging. We will make a chart from those numbers at the next level.

What to take from this lesson

Sao chéping shifts A12 → A13, B2 → B3. One row down, one address down.
One formula for a week is enough. The square does the rest.
A hard number in a formula does not shift — so write cell addresses.
Daily left = that day. A running till = yesterday plus today. A change in the middle behaves differently for them.
#VALUE! after dragging = there is text in the row. The same number in the whole column = you forgot references.