Complex Formulas and Functions
Senior 2 · ICT
Complex Formulas and Functions
Lesson 1 of 5
20 min
Let's begin
A hardware shop in Musanze charges 18% VAT (value added tax) on every item. The VAT rate is typed once, in cell F1. The shopkeeper writes one formula for the first item and copies it down the list, but every row after the first shows a VAT of 0. The formula was correct for the first row. What went wrong when it was copied? The answer is in how spreadsheets treat cell references.
Everything here works the same way in LibreOffice Calc and Microsoft Excel unless a difference is pointed out. Besides the arithmetic operators (+ - * / ^ %), spreadsheets have three more kinds of operator:
| Kind | Operators | Example | Result |
|---|---|---|---|
| Comparison: compares two values and gives TRUE or FALSE | = (equal), <> (not equal), <, >, <=, >= | =B2>=50 with B2 = 78 | TRUE |
| Text: joins pieces of text | & | ="S2"&"B" | S2B |
| Reference: describes a range | : (colon) | B2:D2 | the cells B2, C2 and D2 |
A complex formula combines several operators, and often functions and references, in one calculation. The order of operations still applies: brackets, powers, multiplication and division, then addition and subtraction. For example, a student's mean mark is =(B2+C2+D2)/3. Without the brackets, =B2+C2+D2/3 divides only D2 by 3.
When you copy a formula, its references normally change by the same number of rows and columns as the move. These are relative references. They are what makes the fill handle useful:
Sometimes a reference must not change, because every row uses the same cell, such as the VAT rate. A dollar sign ($) before the column letter and before the row number locks it: $F$1 is an absolute reference. It stays $F$1 wherever the formula is copied.
F1 holds 18%. Prices are in B2:B4: 2,000, 1,500 and 3,500 Frw.
In the wrong version, F1 is relative, so it slides down to F2 and F3, which are empty and count as 0. In the right version, B2 is still relative (each row needs its own price) but $F$1 is absolute, so every row multiplies by the same 18%. Check: 2,000 × 18% = 360; 1,500 × 18% = 270; 3,500 × 18% = 630.
A mixed reference locks only one part:
Example: a multiplication table has the numbers 1 to 5 in B1:F1 and in A2:A6. In B2, type =$A2*B$1 and copy it across and down. Every cell multiplies its own row's number in column A by its own column's number in row 1. In D4 the formula has become =$A4*D$1, which is 3 × 3 = 9.
To switch between the four types while editing a reference, press Shift+F4 in Calc or F4 in Excel: A1 → $A$1 → A$1 → $A1 → A1.
Tip
The dollar sign is a lock. $ before the letter locks the column; $ before the number locks the row. Ask: "When I copy this, should this cell move?" If not, lock it.
=A1+B1 is in C1. What does it become when copied to C4?
Solution: It moves down 3 rows, so both row numbers go up by 3: =A4+B4.
=$A$1*B1 is in C1. What does it become when copied to D3?
Solution: The move is 1 column right and 2 rows down. $A$1 is absolute and does not change; B1 becomes C3. Result: =$A$1*C3.
=$A2*B$1 is in B2. What does it become when copied to C3?
Solution: 1 column right, 1 row down. $A2 keeps column A but its row becomes 3: $A3. B$1 keeps row 1 but its column becomes C: C$1. Result: =$A3*C$1.
What does ="Kigali"&" "&"City" show?
Solution: Kigali City. The & joins the three pieces, including the space in the middle.
Remember
Sources and credits
Wrong: C2 holds =B2*F1 -> 360
copied to C3 becomes =B3*F2 -> 0 (F2 is empty)
copied to C4 becomes =B4*F3 -> 0
Right: C2 holds =B2*$F$1 -> 360
copied to C3 becomes =B3*$F$1 -> 270
copied to C4 becomes =B4*$F$1 -> 630