• 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.

Operators in Calc and Excel
OperatorMeaningExampleResult
+Add=7+310
-Subtract=7-34
*Multiply=7*321
/Divide=21/37
^Power=2^38
%Per cent=200*10%20
&Join text="Sik"&"hora"Sikhora
=, <>, >, <, >=, <=Compare (equal, not equal, greater ...)=5>3TRUE

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

  1. Brackets
  2. Powers (^)
  3. × and ÷ Left to right.
  4. + and − Left to right.

Worked example 1: same numbers, different answers

  1. =10+4*2 → multiply first: 4 × 2 = 8, then 10 + 8 = 18.
  2. =(10+4)*2 → brackets first: 14, then 14 × 2 = 28.
  3. =20/4*2 → division and multiplication share a level, so go left to right: 20 ÷ 4 = 5, then 5 × 2 = 10 (not 20 ÷ 8).
  4. =2+3^2*2 → power first: 9; then 9 × 2 = 18; then 2 + 18 = 20.
  5. 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:

A2 changes row by row; $E$1 never moves
CellFormula after copyingPriceGST
B2=A2*$E$120036
B3=A3*$E$145081
B4=A4*$E$11200216

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. 1. What does the formula =5+3*4 show?

    1. 17
    2. 32
    3. 20
    4. 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. 2. What does the formula =(5+3)*4 show?

    1. 17
    2. 20
    3. 32
    4. 60
    Show answer

    Answer: C. Brackets are worked first: 5 + 3 = 8, then 8 × 4 = 32.

  3. 3. Cell E2 holds =C2+D2. You copy E2 and paste it into E5. What formula is in E5?

    1. =C2+D2
    2. =C3+D3
    3. =C5+D5
    4. =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. 4. C3 holds =B3*$F$1. It is copied to D6. What formula is in D6?

    1. =B6*$F$1
    2. =C6*$G$4
    3. =C6*F1
    4. =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. 5. While editing a formula in LibreOffice Calc, which key cycles a reference through relative, absolute and mixed forms?

    1. F4
    2. Shift+F4
    3. Ctrl+$
    4. 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.

Privacy · Terms · Refunds · Delivery