AVERAGE function
AVERAGE adds up the numbers you give it and divides by how many there are, returning the arithmetic mean. It is the everyday way to summarize a column of figures.
Example
Read it as a sentence: find the average of the numbers in A1:A8, which gives 5.
Excel shows 5
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
AVERAGE(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - First number, array or range.
-
number2(optional) - Further numbers, arrays or ranges.
Returns: The arithmetic mean of the numbers supplied.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=AVERAGE({2,4,4,4,5,5,7,9}) | 5 | Array constant |
=AVERAGE(10,"20",TRUE) | 10.33333333 | Direct args: number, text-number, TRUE |
=AVERAGE(5) | 5 | Single number |
=AVERAGE({1,"2",TRUE,3}) | 2 | Array with text and boolean |
=AVERAGE(Data!A1:A8) | 5 | Range of numbers |
=AVERAGE(Data!B1:B8) | 26.66666667 | Mixed range: num, text-number, TRUE, blank, text, FALSE |
=AVERAGE(10,"abc") | #VALUE! | Direct arg: non-numeric text |
=AVERAGE(Data!C1:C5) | #N/A | Range containing #N/A |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
C
1
2
10
1
2
4
20
2
3
4
TRUE
=NA()
4
4
4
5
5
abc
5
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- Text, logicals and empty cells in a range are skipped, but a typed-in zero counts.
- Hidden rows are still included; use SUBTOTAL or AGGREGATE to skip them.
- Returns #DIV/0! if nothing numeric is found.
Where it works
- Excel
- AVERAGE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .