AVERAGE function
AVERAGE adds up the numbers you give it and divides by how many numbers there are. Inside a range it skips empty cells and text but counts zeros, so a week with 0 sales pulls the average down while a week that hasn't been entered yet doesn't.
- Available in
- Every current version
- Category
- Statistical functions
- Returns
- The arithmetic mean of the numbers, or #DIV/0! if there are no numbers to average.
- Google Sheets
- Also available in Google Sheets.
Syntax
=AVERAGE(number1, [number2], ...)
| Argument | Required? | What it is |
|---|---|---|
number1 | Required | The first number, cell or range to average. |
number2 | Optional | More numbers, cells or ranges, up to 255 in total. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =AVERAGE(B2:B7) | 35.6 | Week 3's zero counts; the empty week 6 is skipped |
| F3 | =AVERAGE(B2:B3,B5:B6) | 44.5 | Two ranges in one formula leave out week 3 |
| F4 | =ROUND(AVERAGE(B2:B7),0) | 36 | Rounded to a whole number for a report |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Week | Tickets | Average | Result | ||
| Week 1 | 42 | All weeks | 35.6 | ||
| Week 2 | 38 | Skip week 3 | 44.5 | ||
| Week 3 | 0 | Rounded | 36 | ||
| Week 4 | 51 | ||||
| Week 5 | 47 | ||||
| Week 6 |
Practice AVERAGE
From lesson 2, SUM, AVERAGE, MIN, MAX & COUNT. Graded instantly, then re-checked on changed data.
Average, best and worst
E3 (average daily sales), E4 (best day) and E5 (worst day), each from B2:B8.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The average is lower than expected | Zeros are included. If a 0 means "no data yet", leave the cell empty or use AVERAGEIF(range,"<>0"). |
| #DIV/0! | There are no numbers in the range, often because the values are stored as text. Convert them with VALUE or Text to Columns. |
| Numbers typed as text are ignored | Values pasted from another system can look like numbers but sit left-aligned as text. AVERAGE skips them silently. |
Tips
- To average only some rows, use AVERAGEIF or AVERAGEIFS instead of building the list by hand.
- The status bar shows the average of any selected cells, which is a quick way to check a formula.
Questions
Does AVERAGE ignore blank cells?
Yes. Empty cells inside a range are skipped, so they don't count toward the divisor. Cells containing 0 are counted.
How do I average and ignore zeros?
Use =AVERAGEIF(B2:B7,"<>0"). It averages only the cells that aren't zero.
What is the difference between AVERAGE and MEDIAN?
AVERAGE is the sum divided by the count. MEDIAN is the middle value, which is better when a few extreme numbers would distort the average.
Lessons that use AVERAGE
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.