Use =AVERAGE(B2:B7) to find the mean of numeric cells in Google Sheets. The function adds the numbers and divides by how many numeric values it finds. Blanks and text are excluded; zero is included.
Calculate the Average of a Column
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Student | Score |
|---|---|
| Maya | 74 |
| Noah | 88 |
| Ari | 80 |
| Lena | 92 |
| Omar | 70 |
| Ava | 88 |
Enter this formula in E2:
=AVERAGE(B2:B7)The six scores total 492, so the average is 82. E2 updates when a score changes. A missing score would be excluded rather than treated as zero.
AVERAGE Function Syntax
=AVERAGE(value1, [value2, ...])Supply a number, cell, or range as the first argument. Additional arguments can be more cells, ranges, or numbers. A short literal list works too:
=AVERAGE(10,20,30)This returns 20. Keep the output cell outside any referenced range to avoid a circular reference.
Average Across Rows and Multiple Columns
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Student | Test 1 | Test 2 |
|---|---|---|
| A | 80 | 90 |
| B | 75 | 85 |
| C | 88 | 92 |
| D | 70 | 80 |
| E | 85 | 91 |
=AVERAGE(B13:C17)The result is 83.6. All ten scores form one group, regardless of which test column contains them. AVERAGE returns one combined mean, not a separate result for each row.
=AVERAGE(B13:C13)This returns 85 for the first student. Copying a row formula down gives each student a separate average. Use BYROW when you need an automatic row-by-row array calculation.
Decide Whether Missing Scores Should Count as Zero
Enter this dataset starting in A33. Leave cells marked “(blank)” empty.
| Entry | Score |
|---|---|
| A | 10 |
| B | 0 |
| C | (blank) |
| D | Pending |
| E | 20 |
=AVERAGE(B34:B38)The result is 10, calculated from 10, 0, and 20. The blank cell and Pending label do not increase the denominator.
Only enter zero when it represents an actual zero score. A blank representing missing information and a zero representing no points should not be interchangeable.
If a range contains no numeric values, AVERAGE returns #DIV/0!. An error inside the data is different from a blank: =AVERAGE({10,NA(),20}) returns #N/A. Investigate the source before masking it.
Average Only Rows That Meet a Condition
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| Student | Status | Score |
|---|---|---|
| A | Pass | 80 |
| B | Fail | 40 |
| C | Pass | 88 |
| D | Pass | 84 |
| E | Fail | 55 |
| F | Pass | 84 |
=AVERAGE(FILTER(C23:C28,B23:B28="Pass"))The four Pass scores are 80, 88, 84, and 84. Their average is 84. The Fail scores are excluded before the mean is calculated.
For this simple condition, AVERAGEIF produces the same result in one function:
=AVERAGEIF(B23:B28,"Pass",C23:C28)If FILTER finds no matching rows, the first formula returns #N/A. An interface filter also does not make AVERAGE ignore hidden records; use SUBTOTAL for averages based on visible rows.
Round the Calculated Average
=ROUND(AVERAGE(78,81,82),1)The unrounded mean is 80.3333…; ROUND returns 80.3. This changes the result used by later calculations. Number formatting changes only the displayed decimal places.
A plain average gives each numeric entry equal influence. If scores have different weights, use AVERAGE.WEIGHTED instead. Averaging several group averages equally can be misleading when their group sizes differ.
Other Google Sheets articles you may also like