AVERAGE Function in Excel, Google Sheets, Airtable & Notion
AVERAGE adds up the numbers you give it and divides by how many numbers there are. Empty cells and text inside a range are skipped, not counted as zero.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | AVERAGE(number1, [number2], ...) |
| Google Sheets | ✔ Built in | AVERAGE(value1, [value2, ...]) |
| Airtable | ✔ Built in | AVERAGE(number1, [number2, ...])Averages fields of one record. To average a field across linked records, use a rollup field with AVERAGE(values). |
| Notion | ✔ Built in | mean(number | list, ...)Notion calls it mean: mean(prop("Scores")) or prop("Orders").map(current.prop("Amount")).mean(). Lists must contain numbers only. |
| Excel | =AVERAGE(A2:A7) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =AVERAGE(A2:A7) | open sheets.new ↗ |
| Airtable | AVERAGE(72, 85, 90, 64, 88) | |
| Notion | mean([72, 85, 90, 64, 88]) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Score 72 85 90 64 absent 88
Text values are left out of the Airtable and Notion formulas, because those apps would not skip them.
Arguments
| Argument | What it means |
|---|---|
number1 | The first number, cell or range to average. |
[number2], ... optional | More numbers or ranges, up to 255 arguments. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=AVERAGE(2, 4, 9) | 5 | (2 + 4 + 9) / 3. | |
=AVERAGE(A2:A7) | 79.8 | Tester data: the text “absent” is ignored, so the total of 399 is divided by 5, not 6. | |
=AVERAGE(A2:A7, 100) | 83.1667 | Ranges and constants can be mixed: 499 / 6. | |
=IFERROR(AVERAGE(B2:B10), 0) | 0 if B2:B10 has no numbers | Replaces the #DIV/0! you get when there is nothing to average. |
Examples use Excel / Google Sheets syntax unless noted.
How AVERAGE works
AVERAGE is the plain arithmetic mean: total divided by count. What it counts is the important part. Inside a range, empty cells, text and TRUE/FALSE values are ignored, so a blank for a missing test score does not drag the average down. A cell containing 0 is a number and is counted. If you want blanks to count as zero, put zeros in them; if you want zeros to be left out, use AVERAGEIF with "<>0".
When there are no numbers at all, AVERAGE returns #DIV/0!. Wrap it in IFERROR to show a blank or a message instead. A single error value inside the range (such as #N/A from a failed lookup) also makes the whole result an error. The mean is pulled around by outliers; for skewed data such as salaries or house prices, MEDIAN is often a better “typical” value.
Airtable's AVERAGE works on fields of the current record; to average a column across records, use a rollup field with AVERAGE(values) (or the summary bar of a view). Notion names the function mean and accepts numbers or lists, for example a rollup or prop("Line items").map(current.prop("Price")).mean().
Frequently Asked Questions
- Does AVERAGE count blank cells as zero?
- No. Blank cells and text in a range are skipped. Cells that contain 0 are counted.
- Why does AVERAGE return #DIV/0!?
- There were no numbers to average, often because the values are stored as text. Convert them with VALUE or wrap the formula in IFERROR.
- How do I average only some rows?
- Use AVERAGEIF, or
AVERAGEIFSfor several conditions.