How to average numbers in Excel
Formula (Excel and Google Sheets)
=AVERAGE(B2:B7)Use =AVERAGE(B2:B7), which adds the numbers and divides by how many there are, skipping blank cells.
- Result on the example
- 69.80 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
AVERAGE adds the numbers and divides by the count of numbers. Ben's blank cell (not graded yet) is skipped, so the total of 349 is divided by 5, not 6. Dan's 0 is a real score and counts, which pulls the average down to 69.8.
=AVERAGE(B2:B7)Result69.8
B2:B76 rows × 1 col: 88, (empty), 94, 0, …A range: 6 cells in one column.
AVERAGE(B2:B7)69.8AVERAGE: Arithmetic mean of the numbers.- number1
B2:B76 rows × 1 col: 88, (empty), 94, 0, …
- number1
In Google Sheets
The same formula works in Google Sheets: =AVERAGE(B2:B7). We ran it in Google Sheets mode on the same data and it returns 69.80.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:B7)/COUNT(B2:B7) | 69.80 | The same average built from its parts. COUNT counts only cells with numbers. |
| =SUM(B2:B7)/ROWS(B2:B7) | 58.17Different result | Dividing by the number of rows. Counts the blank as a score of 0: 58.17. |
Common mistakes
- A 0 and a blank are different to AVERAGE: zeros count, blanks don't. If 0 means "no data", use
=AVERAGEIF(B2:B7,"<>0"). - Numbers stored as text are skipped, like blanks, so the average quietly covers fewer students.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Average numbers
E1, calculate the average test score in B2:B7.Questions
How do I average several separate ranges?
List them: =AVERAGE(B2:B7,D2:D7).
How do I round the average?
Wrap it: =ROUND(AVERAGE(B2:B7),1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.