Know and understand the order in which mathematical operations are performed including the use brackets to make sure that formulae work

Topic 20: Spreadsheets – Order of Operations

Learning Objective

Know and understand the order in which mathematical operations are performed, including the use of brackets to make sure that formulae work correctly in a spreadsheet.

Why the Order of Operations Is Important

When a formula is entered into a cell, the spreadsheet program evaluates the expression automatically. If the intended sequence of calculations is not clear, the program will apply its default precedence rules, which may produce a result different from what the user expects. Using brackets (parentheses) allows the user to control the exact order in which parts of the formula are calculated.

Standard Precedence Rules (BODMAS / PEMDAS)

The following table shows the hierarchy of operators that a spreadsheet follows when no brackets are used.

How Spreadsheets Apply the Rules

1. First evaluate any expressions inside parentheses.
2. Next evaluate exponentiation (the ^ operator).
3. Then perform all multiplication and division from left to right.
4. Finally perform addition and subtraction from left to right.

Using Brackets to Control Evaluation

Brackets can be nested to any depth. The innermost pair is evaluated first.

Example 1 – Without brackets:

=A1 + B1 * C1

If A1 = 2, B1 = 3, C1 = 4 then the spreadsheet calculates:

\$2 + 3 \times 4 = 2 + 12 = 14\$

Example 2 – With brackets to change the order:

=(A1 + B1) * C1

The same cell values give:

\$ (2 + 3) \times 4 = 5 \times 4 = 20 \$

Example 3 – Nested brackets:

=((A1 + B1) ^ 2) / (C1 - D1)

Assuming D1 = 1, the calculation proceeds as:

\$\frac{(2 + 3)^2}{4 - 1} = \frac{5^2}{3} = \frac{25}{3} \approx 8.33\$

Common Pitfalls

• Forgetting to close a parenthesis – the formula will return a #VALUE! or #REF! error.
• Relying on default precedence when the intended order is different – always use brackets for clarity.
• Mixing text strings with numbers without proper conversion – use VALUE() or ensure the cell contains a numeric value.

Practice Questions

1. Given A1 = 5, B1 = 2, C1 = 3, what result does the formula =A1 - B1 ^ C1 produce?
2. Rewrite the formula in question 1 so that subtraction occurs after the exponentiation is completed.
3. Enter a formula that adds the values in A1 and B1, multiplies the sum by the value in C1, then subtracts the value in D1. Show the formula and the order of evaluation.

Answers to Practice Questions

1. Exponentiation first: \$2 ^ 3 = 8\$, then subtraction: \$5 - 8 = -3\$.
2. Use brackets to force subtraction first: =(A1 - B1) ^ C1. This gives \$(5 - 2)^3 = 3^3 = 27\$.
3. Formula: =(A1 + B1) * C1 - D1.
   

   Evaluation steps:

   1. Parentheses: \$A1 + B1\$.
   2. Multiplication: result of step 1 multiplied by \$C1\$.
   3. Subtraction: result of step 2 minus \$D1\$.