Excel track · Exercise 9 of 86
AVERAGE: mean of a score list
Turn five scores into one class average that updates itself.
Beginner
Class test scores
Read-only data · answer in B7
In B7, work out the class average of the five scores.
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 85 |
| 3 | Bo | 90 |
| 4 | Cy | 88 |
| 5 | Dee | 92 |
| 6 | Eli | 87.5 |
| 7 | Class average |
Reference answer
What it does
AVERAGE adds the numbers in a range and divides by how many it found. The five scores here are 85, 90, 88, 92 and 87.5, which come to 442.5, so the answer is 88.5. The divisor is the count of cells holding a number, not the count of rows. A blank cell is skipped; a zero is not.
This exercise wants the class average in B7, taken from B2:B6. Every score is present, so nothing is skipped and the answer lands on 88.5. The count is where a typed sum goes wrong: a sixth student with no score yet leaves AVERAGE dividing by five, while SUM(B2:B6)/6 drags the mean down.
Point the formula at the range instead of typing the five numbers. A corrected score then moves the average on its own, and a student who leaves the class stops counting without anyone editing the formula.
Syntax
=AVERAGE(number1, [number2], ...)Worked example
On Class test scores, =AVERAGE(B2:B6) returns 88.5.
=AVERAGE(B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#DIV/0! | The range holds no numbers at all, so there is nothing to divide by: usually the formula points at the names column or at blank cells. |
#VALUE! | A word or symbol was handed to AVERAGE as an argument, as in =AVERAGE("n/a"); text sitting inside a range is skipped instead. |
Wrong divisor | A total divided by a typed 6 keeps dividing by 6 after a student leaves, and the mean slides away from the scores. |
At work
Class averages and average order value: one function covers both, and it keeps its count straight when a row is empty.