SUM, AVERAGE, MAX, MIN, COUNT: practice exercises
Summarising a column. 7 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In E2, add up all the quiz scores.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Quiz score (/50) | Summary | ||
| 2 | Grace | 34 | Total marks | ||
| 3 | Tom | 27 | Mean score | 33.9 | |
| 4 | Priya | 45 | Highest | 45 | |
| 5 | Marek | Lowest | 19 | ||
| 6 | Nia | 41 | People who sat it | 10 | |
| 7 | Ollie | 19 | People on the list | 12 | |
| 8 | Lucy | 38 | |||
| 9 | Hamza | 30 | |||
| 10 | Rachel | 44 | |||
| 11 | Sunil | ||||
| 12 | Tanya | 25 | |||
| 13 | Will | 36 |
How it works
These functions take a range such as B2:B6, meaning every cell from B2 to B6. You can type it, or click the first cell and drag to the last.
Blank cells and text are skipped, so people who missed something don't drag the mean down. COUNT counts cells with numbers in. COUNTA counts every cell that isn't empty, text included.
=SUM(B2:B6) =AVERAGE(B2:B6) =COUNT(B2:B6) =COUNTA(A2:A6) =MEDIAN(B2:B6)
Functions and ranges
A function is a ready-made calculation: its name, then its inputs in brackets. Most of these take a range, such as B2:B6, meaning every cell from B2 down to B6.
SUMadds up.AVERAGEgives the mean.MAXandMINgive the highest and lowest.COUNTcounts cells with numbers in.COUNTAcounts every cell that isn't empty, text included.MEDIANgives the middle value when they're in order.
Worked example: SUM
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | Total | ||
| 2 | Amira | 12 | Mean | ||
| 3 | Ben | 20 | Sat the quiz | ||
| 4 | Cara | People | |||
| 5 | Dev | 16 | |||
| 6 | Ewa | Absent |
=SUM(in . Type = and the function name, then open the bracket.=SUM(B2:B6in . Then the range: B2 to B6. Drag down the cells, or type the first and last cell with a colon between them.=SUM(B2:B6)in . Close the bracket and press Enter: 12 + 20 + 16 = 48. The blank cell and the word Absent are skipped.
Worked example: the mean
If one value is far bigger than the rest, such as a director's salary, it pulls the mean up. MEDIAN, the middle value, hardly moves.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | Total | ||
| 2 | Amira | 12 | Mean | ||
| 3 | Ben | 20 | Sat the quiz | ||
| 4 | Cara | People | |||
| 5 | Dev | 16 | |||
| 6 | Ewa | Absent |
=AVERAGE(B2:B6)in . AVERAGE adds up the numbers and divides by how many numbers there are: 48 ÷ 3 = 16. Cara's blank and Ewa's Absent aren't counted, so they don't drag the mean down.
Counting: COUNT and COUNTA
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | Total | ||
| 2 | Amira | 12 | Mean | ||
| 3 | Ben | 20 | Sat the quiz | ||
| 4 | Cara | People | |||
| 5 | Dev | 16 | |||
| 6 | Ewa | Absent |
=COUNT(B2:B6)in E3. COUNT counts the cells with numbers in: 3 people sat the quiz.=COUNTA(A2:A6)in E4. COUNTA counts every cell that isn't empty, text included. Use it on the names to count people: 5.
What you'll practise
- In E2, add up all the quiz scores.
- In E3, find the mean score. Marek and Sunil missed the quiz; their blank cells should be ignored.
- In E4, find the highest score.
- In E5, find the lowest score.
- In E6, count how many people sat the quiz (have a score).
- In E7, count how many people are on the list altogether.
- Liam is the director, and their salary pulls the mean in E3 well above what most people earn. In E2, find the median salary instead: the middle value when the salaries are in order.