Confident Cells

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.

Your turn

In E2, add up all the quiz scores.

ABCDE
1EmployeeQuiz score (/50)Summary
2Grace34Total marks
3Tom27Mean score33.9
4Priya45Highest45
5MarekLowest19
6Nia41People who sat it10
7Ollie19People on the list12
8Lucy38
9Hamza30
10Rachel44
11Sunil
12Tanya25
13Will36

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.

  • SUM adds up. AVERAGE gives the mean.
  • MAX and MIN give the highest and lowest.
  • COUNT counts cells with numbers in. COUNTA counts every cell that isn't empty, text included.
  • MEDIAN gives the middle value when they're in order.

Worked example: SUM

ABCDE
1NameScoreTotal
2Amira12Mean
3Ben20Sat the quiz
4CaraPeople
5Dev16
6EwaAbsent
  1. =SUM( in . Type = and the function name, then open the bracket.
  2. =SUM(B2:B6 in . Then the range: B2 to B6. Drag down the cells, or type the first and last cell with a colon between them.
  3. =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.

ABCDE
1NameScoreTotal
2Amira12Mean
3Ben20Sat the quiz
4CaraPeople
5Dev16
6EwaAbsent
  1. =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

ABCDE
1NameScoreTotal
2Amira12Mean
3Ben20Sat the quiz
4CaraPeople
5Dev16
6EwaAbsent
  1. =COUNT(B2:B6) in E3. COUNT counts the cells with numbers in: 3 people sat the quiz.
  2. =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

  1. In E2, add up all the quiz scores.
  2. In E3, find the mean score. Marek and Sunil missed the quiz; their blank cells should be ignored.
  3. In E4, find the highest score.
  4. In E5, find the lowest score.
  5. In E6, count how many people sat the quiz (have a score).
  6. In E7, count how many people are on the list altogether.
  7. 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.