Lessons 1–2 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
2
Cell references: relative, absolute and mixed
Textbook: pp. 8–10
GoalTells relative, absolute and mixed references apart and says how a reference changes when a formula is copied.
New words
relative reference such as B2: it shifts when the formula is copied · nisbiy murojaatabsolute reference such as $B$2: it always points to the same cell · absolut murojaatmixed reference such as $B2 or B$2: only the column or only the row is fixed · aralash murojaatcell block (range) such as B2:C5: a rectangle of neighbouring cells · katak bloki
Explanation
A cell address inside a formula is called a reference. A relative reference (B2) changes when the formula is copied, according to how far it moves: copied one row down, B2 becomes B3. An absolute reference ($B$2) always points to the same cell, because the $ sign fixes both the column and the row. In a mixed reference only one part is fixed: the column in $B2, the row in B$2. In Windows the F4 key cycles through the types (B2, $B$2, B$2, $B2). A shared number such as a tax rate or an exchange rate sits in one cell and is joined to every formula by an absolute reference; if the value changes, all the results update at once. A block of neighbouring cells is written with its two corner cells: B2:C5 is 8 cells in 2 columns and 4 rows.
Worked examples
Prices are in B2:B4: 12 000, 8 500 and 15 000 so‘m; the 12% VAT rate is in E1. C2 gets =B2*$E$1 (1 440). Copied down to C4, C3 holds =B3*$E$1 (1 020) and C4 holds =B4*$E$1 (1 800): B changed as a relative reference, while $E$1 stayed.
A multiplication table: A2:A4 hold 2, 3, 4 and B1:D1 hold 5, 6, 7. B2 gets =$A2*B$1 and is copied over the whole block. D4 shows =$A4*D$1 = 4·7 = 28 and C3 shows =$A3*C$1 = 3·6 = 18. Because the column letter A and the row number 1 are fixed, one entry serves the whole table.
Class activity
“The dollar sign”: the cards say B2, $B$2, B$2 and $B2. The teacher says “the formula was copied from C2 to E4” and students write what each reference becomes (correct answers: D4, $B$2, D$2, $B4).
Practice
1
The formula =B2*$E$1 is copied from C2 to C5. What formula is in C5?
=B5*$E$1
2
A product costs 8 500 so‘m and VAT is 12%. How many so‘m is the VAT?
1020
3
How many cells are in the block B2:C5?
8
4
Why is the cell with the tax rate referred to by an absolute reference?
So the rate cell does not shift when the formula is copied down; then all rows use the same rate.