Learning Excel: AVERAGE
The average
Ruth from Green Fingers drops a pile of weighing slips on the table. "Who had the best harvest? And what's normal for a plot like that?" That starts with an average.
Another function
Just like SUM, AVERAGE is a function: a name, with its arguments in brackets.
AVERAGE adds up the numbers and divides by how many there are. D12 holds =AVERAGE(D2:D11): the average kilos of tomatoes per plot.
=AVERAGE(D2:D11)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 |
| 5 | P04 | 25 | ||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 |
| 12 | Average | 18.44 | ||||
| 13 | Highest | |||||
| 14 |
Empty cells don't count
Look at plots P04 and P09: there are no tomatoes there. AVERAGE skips those empty cells, so it divides by 8, not by 10.
That's usually what you want: a plot without tomatoes doesn't have a tomato harvest of 0 kilos, it simply doesn't have one.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 |
| 5 | P04 | 25 | ||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 |
| 12 | Average | |||||
| 13 | Highest | |||||
| 14 |
Try it yourself
In F12, work out the average kilos of beans per plot.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | ||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | |
| 5 | P04 | 25 | |||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | ||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | ||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | |
| 12 | Average | ||||||
| 13 | Highest | ||||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson