- Foundation
- About 16 min
- 5 practice questions
Formulas: operators, order of operations, relative and absolute references
Write formulas that start with =, predict results using the order of operations, and copy formulas correctly with relative, absolute ($A$1) and mixed references.
If something looks wrong, please tell us.
A formula is what turns a grid of numbers into a calculator. Instead of working out a bill on paper and typing the answer, you tell the cell how to calculate it, and it recalculates by itself whenever a number changes. The rules here are the same in Calc and Excel.
Every formula starts with =
A formula begins with an equals sign. Type =25+15 in a cell and press Enter: the cell shows 40, while the formula bar still shows =25+15. Without the equals sign, 25+15 is stored as text and nothing is calculated. Formulas normally use cell addresses rather than fixed numbers: if A1 holds 25 and B1 holds 15, then =A1+B1 shows 40, and changing A1 to 30 makes it show 45 at once.
| Operator | Meaning | Example | Result |
|---|---|---|---|
| + | Add | =7+3 | 10 |
| - | Subtract | =7-3 | 4 |
| * | Multiply | =7*3 | 21 |
| / | Divide | =21/3 | 7 |
| ^ | Power | =2^3 | 8 |
| % | Per cent | =200*10% | 20 |
| & | Join text | ="Sik"&"hora" | Sikhora |
| =, <>, >, <, >=, <= | Compare (equal, not equal, greater ...) | =5>3 | TRUE |
Order of operations
When a formula has several operators, the program does not simply work left to right. It follows an order much like BODMAS: brackets first, then powers (^), then multiplication and division (left to right), then addition and subtraction (left to right). Joining text with & and comparisons come after arithmetic. When in doubt, add brackets - they make the formula clearer and the result certain.
Order of operations
- Brackets
- Powers (^)
- × and ÷ Left to right.
- + and − Left to right.
Worked example 1: same numbers, different answers
- =10+4*2 → multiply first: 4 × 2 = 8, then 10 + 8 = 18.
- =(10+4)*2 → brackets first: 14, then 14 × 2 = 28.
- =20/4*2 → division and multiplication share a level, so go left to right: 20 ÷ 4 = 5, then 5 × 2 = 10 (not 20 ÷ 8).
- =2+3^2*2 → power first: 9; then 9 × 2 = 18; then 2 + 18 = 20.
- If A1 = 50 and B1 = 8, =A1-B1*2 gives 50 − 16 = 34, while =(A1-B1)*2 gives 42 × 2 = 84.
Relative references: formulas that adjust
When you copy a formula, the addresses in it change by the same number of rows and columns as the move. This is called a relative reference. Suppose C2 holds =A2*B2 (quantity × rate). Copy it down to C3 and it becomes =A3*B3; to C10 it becomes =A10*B10. That is exactly what you want for a column of bills: one formula, filled down with the fill handle.
Absolute references: the $ sign
Sometimes every row must use the same cell, such as a tax rate typed once. Put a dollar sign before the column letter and the row number - $E$1 - and the address stays fixed when copied. This is an absolute reference. A mixed reference fixes only one part: $E1 keeps column E but lets the row change; E$1 keeps row 1 but lets the column change. To cycle through the four forms while editing, press F4 in Excel or Shift+F4 in Calc.
Three kinds of reference
-
Relative (A2)
- Changes when copied
- Moves by the same rows and columns
-
Absolute ($E$1)
- Stays fixed when copied
- For a cell every copy must share
-
Mixed ($E1 or E$1)
- Fixes only one part
- $E1 keeps column E
- E$1 keeps row 1
Worked example 2: GST at one fixed rate
E1 holds 18% (stored as 0.18). Prices are in A2:A4, and B2 holds =A2*$E$1. Fill B2 down to B4:
| Cell | Formula after copying | Price | GST |
|---|---|---|---|
| B2 | =A2*$E$1 | 200 | 36 |
| B3 | =A3*$E$1 | 450 | 81 |
| B4 | =A4*$E$1 | 1200 | 216 |
Without the dollar signs, B2 would hold =A2*E1, and copying it to B3 would give =A3*E2. E2 is empty, which counts as 0, so the GST would wrongly show 0. Whenever a copied formula gives zeros or strange results, check whether a reference should have been absolute.
Brackets → powers → × and ÷ (left to right) → + and − (left to right). $ fixes what follows it: $A$1 fixed, $A1 column fixed, A$1 row fixed.
Key takeaways: start every formula with =, refer to cells rather than typing numbers, use brackets to control the order, and use $ for any cell that every copy must share. F4 (Excel) and Shift+F4 (Calc) switch between relative, absolute and mixed forms.
Check your understanding
-
1. What does the formula =5+3*4 show?
- 17
- 32
- 20
- 12
Show answer
Answer: A. Multiplication comes before addition: 3 × 4 = 12, then 5 + 12 = 17. Working left to right would wrongly give 8 × 4 = 32.
-
2. What does the formula =(5+3)*4 show?
- 17
- 20
- 32
- 60
Show answer
Answer: C. Brackets are worked first: 5 + 3 = 8, then 8 × 4 = 32.
-
3. Cell E2 holds =C2+D2. You copy E2 and paste it into E5. What formula is in E5?
- =C2+D2
- =C3+D3
- =C5+D5
- =F5+G5
Show answer
Answer: C. The references are relative. Moving from row 2 to row 5 adds 3 to every row number, and the column does not change, so =C2+D2 becomes =C5+D5.
-
4. C3 holds =B3*$F$1. It is copied to D6. What formula is in D6?
- =B6*$F$1
- =C6*$G$4
- =C6*F1
- =C6*$F$1
Show answer
Answer: D. The move is 1 column right and 3 rows down. The relative B3 becomes C6. $F$1 has both parts fixed by dollar signs, so it stays $F$1.
-
5. While editing a formula in LibreOffice Calc, which key cycles a reference through relative, absolute and mixed forms?
- F4
- Shift+F4
- Ctrl+$
- F2
Show answer
Answer: B. In Calc the shortcut is Shift+F4. In Excel the same job is done by F4 alone. F2 is the key for editing a cell in both programs.
Sign in to keep track of the lessons you finish.