Ranges and functions · AVERAGE, MAX and MINWorks in: all current Excel versions, Google Sheets
AVERAGE
AVERAGE adds the numbers in a range and divides by how many numbers there are: =AVERAGE(D2:D9) is what a rep sold in August, on average.
It skips empty cells and text, but a 0 counts: the average of 10, an empty cell and 20 is 15, while the average of 10, 0 and 20 is 10.
Syntax
AVERAGE(range)
- range
- the numbers to average, like
D2:D9.
Common mistakes
- Typing a 0 for “no data yet”: it drags the average down. Leave the cell empty instead.
- Averaging a range that includes the total row, which counts the total as one more rep.
Tap Try it to average August.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
| 10 | ||||||
| 11 | Average |
Tap Try it to type =AVERAGE(D2:D9) into D11.
Go deeper: SUM, AVERAGE, COUNT, MIN and MAX