- Practice
- About 14 min
- 5 practice questions
Formulas and basic functions
Write and evaluate formulas using SUM, AVERAGE, MAX, MIN and COUNT and the order of operations.
If something looks wrong, please tell us.
Every formula in a spreadsheet starts with the equals sign (=). Without it, the cell holds plain text. Exams ask what a formula returns, so you must evaluate it by hand.
Common functions
| Function | Returns |
|---|---|
| =SUM(A1:A5) | Total of the numbers in the range |
| =AVERAGE(A1:A5) | Mean of the numbers |
| =MAX(A1:A5) | Largest value |
| =MIN(A1:A5) | Smallest value |
| =COUNT(A1:A5) | How many cells hold numbers |
| =IF(A1>50,"Pass","Fail") | Pass when A1 is above 50, otherwise Fail |
Order of operations
Brackets first, then exponents, then multiplication and division (left to right), then addition and subtraction. So =2+3*4 gives 14, but =(2+3)*4 gives 20.
Worked examples
Example 1. Cells A1 to A4 hold 10, 20, 30 and 40. What do =SUM(A1:A4) and =AVERAGE(A1:A4) give? SUM = 100. AVERAGE = 100 / 4 = 25.
Example 2. B1 holds 8 and B2 holds 4. What does =B1+B2*2 give? Multiplication first: 4 x 2 = 8, then 8 + 8 = 16.
A formula starts with =. Multiply and divide before add and subtract, unless brackets say otherwise.
Key takeaways: start with =, follow the order of operations and learn the five basic functions.
Check your understanding
-
1. Every formula in a spreadsheet must begin with
- =
- #
- @
- $
Show answer
Answer: A. A formula starts with the equals sign; without it the entry is not calculated.
-
2. What does =5+10/5*2 return?
- 6
- 3
- 15
- 9
Show answer
Answer: D. Division and multiplication first, left to right: 10 / 5 = 2, then 2 x 2 = 4. Then 5 + 4 = 9.
-
3. B1 holds 6 and B2 holds 3. What does =B1+B2*4 return?
- 36
- 13
- 18
- 24
Show answer
Answer: C. Multiplication first: 3 x 4 = 12, then 6 + 12 = 18.
-
4. Three cells hold 12, 18 and 30. What is the AVERAGE of them?
- 60
- 20
- 18
- 30
Show answer
Answer: B. (12 + 18 + 30) / 3 = 60 / 3 = 20.
-
5. A1 holds 50. What does =IF(A1>50,"Pass","Fail") show?
- Pass
- 50
- Fail
- TRUE
Show answer
Answer: C. The test is 'greater than 50'. 50 is not greater than 50, so the test is false and IF returns Fail.
Sign in to keep track of the lessons you finish.