- Foundation
- About 13 min
- 5 practice questions
Common functions: SUM, AVERAGE, MAX, MIN and COUNT
Use the standard functions on a range and work out what each one returns.
If something looks wrong, please tell us.
Adding 50 cells with plus signs would be tiring. Functions are ready-made formulas that do such jobs on a whole range.
Main functions
| Function | Does | Example |
|---|---|---|
| SUM | Adds the numbers | =SUM(B2:B6) |
| AVERAGE | Mean of the numbers | =AVERAGE(B2:B6) |
| MAX | Largest number | =MAX(B2:B6) |
| MIN | Smallest number | =MIN(B2:B6) |
| COUNT | How many cells hold numbers | =COUNT(B2:B6) |
| IF | Chooses between two results | =IF(B2>=40,"Pass","Fail") |
The parts of a function
A function has a name, then brackets holding its arguments, here the range. The colon in B2:B6 means every cell from B2 to B6.
Worked examples
Suppose B2 to B6 hold the marks 40, 55, 60, 45 and 50.
Example 1. =SUM(B2:B6) = 40 + 55 + 60 + 45 + 50 = 250.
Example 2. =AVERAGE(B2:B6) = 250 / 5 = 50.
Example 3. =MAX(B2:B6) = 60 and =MIN(B2:B6) = 40.
Example 4. If B2 holds 40, what does =IF(B2>=40,"Pass","Fail") show? The test 40 >= 40 is true, so it shows Pass.
SUM adds, AVERAGE means, MAX largest, MIN smallest, COUNT counts numbers, IF decides.
Key takeaways
- Functions work on ranges: =NAME(range).
- COUNT counts numbers only; IF returns one of two results.
- Check your answers by hand on small ranges.
Check your understanding
-
1. B2 to B6 hold 30, 45, 60, 25 and 40. What does =SUM(B2:B6) return?
- 200
- 40
- 60
- 160
Show answer
Answer: A. 30 + 45 + 60 + 25 + 40 = 200.
-
2. B2 to B6 hold 30, 45, 60, 25 and 40. What does =AVERAGE(B2:B6) return?
- 45
- 50
- 40
- 200
Show answer
Answer: C. The sum is 200 and there are 5 numbers, so the mean is 200 / 5 = 40.
-
3. B2 to B6 hold 30, 45, 60, 25 and 40. What does =MIN(B2:B6) return?
- 30
- 60
- 25
- 40
Show answer
Answer: C. MIN gives the smallest number in the range, which is 25.
-
4. A1 to A4 hold 12, the word Ravi, 7 and 9. What does =COUNT(A1:A4) return?
- 4
- 28
- 3
- 1
Show answer
Answer: C. COUNT counts only cells that hold numbers: 12, 7 and 9. The word is not counted, so the answer is 3.
-
5. A1 holds 40. What does =IF(A1>=40,"Pass","Fail") show?
- Pass
- Fail
- TRUE
- 40
Show answer
Answer: A. The test 40 >= 40 is true (equal counts), so IF returns its first result, Pass.
Sign in to keep track of the lessons you finish.