- Foundation
- About 12 min
- 5 practice questions
Formulas and operators
Write simple formulas with + - * / and understand the order in which operators are worked out.
If something looks wrong, please tell us.
The real power of a spreadsheet is that it calculates for you and updates the answer when the numbers change. For that you need formulas.
Writing a formula
A formula starts with = and combines numbers, cell addresses and operators. Typing =B2+B3 adds the values in B2 and B3. Using cell addresses, not typed numbers, means that when B2 changes, the answer changes by itself.
| Operator | Meaning |
|---|---|
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| ^ | Power (exponent) |
Order of operations
A spreadsheet works out brackets first, then powers, then multiplication and division from left to right, then addition and subtraction from left to right. This is the same order as in maths (BODMAS).
Worked examples
Example 1. Evaluate =2+3*4. Multiplication first: 3 x 4 = 12, then 2 + 12 = 14.
Example 2. Evaluate =(2+3)*4. Brackets first: 5, then 5 x 4 = 20.
Example 3. A1 holds 50 and A2 holds 30. What does =A1-A2/10 give? Division first: 30 / 10 = 3, then 50 - 3 = 47.
Example 4. If A1 changes from 50 to 60 in the formula =A1-A2/10, what happens? The result updates to 60 - 3 = 57 automatically.
Start with =. Use cell addresses so answers update. Brackets, power, multiply/divide, add/subtract.
Key takeaways
- Formulas begin with = and use + - * / ^.
- Order: brackets, power, * and /, then + and -.
- Cell addresses keep results up to date.
Check your understanding
-
1. What does =4+6*2 return?
- 20
- 12
- 16
- 48
Show answer
Answer: C. Multiplication comes first: 6 x 2 = 12, then 4 + 12 = 16.
-
2. What does =(4+6)*2 return?
- 20
- 16
- 12
- 48
Show answer
Answer: A. Brackets come first: 4 + 6 = 10, then 10 x 2 = 20.
-
3. What does =3^2 return?
- 6
- 5
- 8
- 9
Show answer
Answer: D. The ^ operator is a power: 3 to the power 2 is 3 x 3 = 9.
-
4. A1 holds 40 and A2 holds 20. What does =A1-A2/4 return?
- 5
- 35
- 30
- 10
Show answer
Answer: B. Division comes before subtraction: 20 / 4 = 5, then 40 - 5 = 35.
-
5. Why is =A1+A2 better than typing the two numbers into the formula?
- The cell can no longer be edited
- The result is always rounded
- The result updates by itself when A1 or A2 changes
- The formula works without the = sign
Show answer
Answer: C. A formula that uses cell addresses recalculates automatically whenever the values in those cells change.
Sign in to keep track of the lessons you finish.