AVERAGE

Returns the arithmetic mean of the numbers in a range.

Syntax

=AVERAGE(number1, [number2], …)

Arguments

number1 - The first number or range.
number2 (optional) - Additional numbers or ranges.

Examples

=AVERAGE(B2:B100)

Returns: Mean of the values

Blank cells are ignored; zeros are counted.

How AVERAGE evaluates

AVERAGE returns the arithmetic mean: the sum of the numeric values divided by how many of them there were. The interesting half of that sentence is the denominator. AVERAGE counts only the cells it actually added, so what it decides to skip changes the answer as much as what it adds. Blank cells are skipped, text is skipped, TRUE and FALSE inside a range are skipped, and none of them enlarge the divisor. A cell containing 0, on the other hand, is a number: it is added and counted, which pulls the mean down.

That distinction between blank and zero is the single most common source of a wrong average. A blank cell and a cell holding 0 look almost identical on screen, but a column of 10, 20 and a blank averages 15, while 10, 20 and 0 averages 10. Worse, a cell that holds =IF(B2="", "", B2) is not blank at all - the empty string it returns is text, so AVERAGE ignores it, while COUNTA counts it. If you need zeros treated as real observations, leave them in; if you need them excluded, use =AVERAGEIF(B2:B100, "<>0").

AVERAGE has two siblings worth knowing. AVERAGEA takes the same arguments but counts text as 0 and TRUE as 1, so a column of numbers with the word "n/a" typed into it averages lower under AVERAGEA than under AVERAGE - the entries are not skipped, they are scored as zero. AVERAGEIF and AVERAGEIFS add conditions. And when there is nothing numeric to divide by, AVERAGE does not return 0 or a blank: it returns #DIV/0!, which is honest but tends to surprise people who expected an empty result from an empty range.

Blanks versus zeros: Empty cells are excluded from both the total and the count. Cells containing 0 are included in both. This is the difference that most often makes a reported average look wrong.
Text in a range: Ignored, including text that looks like a number and including the empty string a formula returns. Neither the total nor the divisor changes.
Booleans: TRUE and FALSE in a referenced range are ignored. Typed directly - =AVERAGE(TRUE, 3) - they count as 1 and 0, giving 2.
Errors: Any error value in the arguments is returned as the result. AVERAGE never steps over a #N/A left behind by a lookup.
Nothing to average: If no numeric value survives the filtering, the result is #DIV/0!, not zero. An entirely blank range, or one holding only text, hits this.
Hidden and filtered rows: Included. AVERAGE has no visibility awareness; use SUBTOTAL(101, range) or AGGREGATE to average only what a filter is showing.

Errors AVERAGE returns

Error	What it means and how to fix it
#DIV/0!	There were no numbers to average - the range is empty, holds only text, or holds only formulas returning "". Wrap it as =IFERROR(AVERAGE(B2:B100), "") when an empty period is expected, but check first that the data is not simply stored as text.
#VALUE!	A literal text argument that cannot be converted, as in =AVERAGE("n/a", 5). Text inside a referenced range never triggers this; only values typed straight into the formula do.
#N/A or another propagated error	One of the referenced cells already holds that error. Fix the source formula, or use =AGGREGATE(1, 6, B2:B100) to average while ignoring errors.
#REF!	The range was deleted out from under the formula. Re-point it at the surviving data.

More examples

=AVERAGE(B2:B25, D2:D25)

Returns: Mean across two separate columns

Both ranges are pooled into one calculation. This is not the average of two averages - a column with more numbers in it carries more weight.

=AVERAGEIF(B2:B100, "<>0")

Returns: Mean of the non-zero entries

The usual way to make zeros behave like blanks. Note the criteria is a string, quotation marks included.

=SUMPRODUCT(B2:B20, C2:C20) / SUM(C2:C20)

Returns: Weighted mean

Values in B, weights in C. AVERAGE cannot weight; this is the standard replacement. It returns #DIV/0! when the weights total zero.

Compatibility

Application	Support
Excel 2007-2016	Supported, together with AVERAGEA, AVERAGEIF and AVERAGEIFS.
Excel 2019	Identical behaviour.
Excel 2021 / Microsoft 365	Identical. Accepts dynamic arrays directly, so =AVERAGE(FILTER(B2:B100, C2:C100="West")) needs no special entry.
Google Sheets	Same rules for blanks, text and zeros. Sheets also offers AVERAGE.WEIGHTED, which Excel has no equivalent of.
LibreOffice Calc	Supported with the same semantics; AVERAGEA also present. The option that converts text in ranges to numbers can change the count as well as the total.

Does AVERAGE Work in Google Sheets?

Yes. AVERAGE uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.

In the ExcelTool editor

The ExcelTool editor implements AVERAGE by collecting numbers with the same rule its SUM uses, then dividing by how many it collected: values inside a range count only when stored as numbers, while a text or boolean argument typed directly into the formula is converted first.

When that collection is empty the editor returns #DIV/0!, matching Excel rather than returning zero, and any error value found in the arguments is returned instead of a mean.

AVERAGEA is implemented separately in the editor and works on a different basis: it keeps every non-blank value, adds numbers and booleans as 1 or 0, contributes nothing for text, and divides by the full count - so text drags the mean down exactly as it does in Excel.

AVERAGEIF and AVERAGEIFS are both available in the editor as well, so a conditional mean does not have to be built out of SUM and COUNT.

You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.