Order of Operations
Understanding the order of mathematical operations is critical when creating complex formulae in spreadsheets
- In spreadsheets, as in mathematics, operations are executed in a specific order, known as BIDMAS or BODMAS
- BIDMAS stands for Brackets, Indices (or powers/exponents), Division and Multiplication (from left to right), Addition and Subtraction (from left to right)
Brackets can be used to specify which operations to perform first, outside of this order.
- For example, in the formula "=A1+2*3", the multiplication will be performed first, resulting in "A1 + 6"
- But if we write the formula as "=(A1+2)3", the operation inside the brackets will be performed first, resulting in "3A1 + 6"
Consider the following example spreadsheet:
|
A |
B |
C |
|
|
1 |
10 |
20 |
|
|
2 |
5 |
15 |
|
|
3 |
=20*2 |
- If you input the formula "=A1+B3" in cell C1, the result will be 50, because B3 is calculated first (20*2=40), and then A1 is added (10+40=50)
- If you input the formula "=(A1+B1)*2" in cell C2, the result will be 60, because A1+B1 is calculated first (10+20=30), and then the result is multiplied by 2 (30*2=60)
It's always a good idea to use brackets to make sure that your formulae work as expected, even if they might not be necessary
- It makes the formula easier to read and understand
- It can prevent errors if the formula is edited in the future
