Excel AVERAGE Function

AVERAGE adds up a group of numbers and divides by how many there are — the everyday arithmetic mean, with no manual summing required. One thing catches people off guard: AVERAGE ignores blank cells and text, but it counts zeros as real values, so a mix of blanks and zeros in the same range can produce two very different results.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and every earlier version back to Excel 97. AVERAGE has no version restrictions, unlike newer functions such as XLOOKUP or LET.

Syntax

=AVERAGE(number1, [number2], ...)

Parameter	Required	Description
`number1`	Yes	The first number, cell, or range you want averaged. Can be a typed number, a single cell reference, or an entire range like B2:B20.
`number2`	No	Additional numbers or ranges to fold into the same average. Add as many as you need, up to 255 arguments total, and Excel treats them all as one combined group.

ℹ

The arithmetic mean is just the everyday "average": add up all the numbers, then divide by how many numbers there are.

Basic Example

You're tracking a store's sales totals for the first half of the year and want one number that represents typical monthly performance.

=AVERAGE(C2:C7)

// C2:C7 = six monthly sales totals, January through June

AVERAGE adds the six totals in C2:C7 and divides by 6. If the store closed for renovations and left May's total (C6) blank, AVERAGE divides by 5 instead of 6, since blank cells drop out of both the sum and the count. A $0 total in June, on the other hand, still counts as a real data point and pulls the average down with it.

How AVERAGE Works

It ignores blank cells and text

AVERAGE skips blank cells and cells containing text entirely; neither factors into the sum or the count. In a range of B2:B10 where B5 is blank and B8 contains the word "N/A", AVERAGE calculates the mean of only the remaining seven numeric cells, dividing by 7, not 9.

It includes zero values

AVERAGE treats a cell containing 0 as a real number, not as missing data. Three test scores of 90, 0, and 80 average to 56.67, not 85, because the 0 (a student who skipped the exam) still counts in the division. If you want zeros excluded, you need AVERAGEIF or AVERAGEIFS instead.

It accepts up to 255 mixed arguments

You can combine individual numbers, single cell references, and multiple non-adjacent ranges in one formula, up to 255 arguments total. =AVERAGE(B2:B10, D2:D10, 100) averages every numeric value across both ranges plus the constant 100, all as one group.

Typed logical values behave differently than referenced ones

Type TRUE and FALSE directly into a formula and AVERAGE treats them as 1 and 0. Reference a cell that contains TRUE or FALSE, and AVERAGE ignores it completely, as if the cell were text. =AVERAGE(TRUE, FALSE) returns 0.5. But if A2 contains TRUE and A3 contains FALSE, =AVERAGE(A2:A3) returns a #DIV/0! error, because AVERAGE skips both values and leaves nothing numeric to average.

⚠

Don't assume logical values behave the same way whether typed or referenced. The results are silently different, and nothing flags the switch.

Common Use Cases

Average a Single Column of Scores

A teacher wants the class average for a quiz stored in a named range.

=AVERAGE(quiz_scores)   // quiz_scores = B2:B28, one score per student

Average Two Separate Periods Together

You want to compare typical performance across Q1 and Q3 while deliberately excluding a promotional Q2.

=AVERAGE(Q1_sales, Q3_sales)

// Q1_sales = B2:B4
// Q3_sales = B8:B10
// Both ranges combine into a single average, Q2 is never included

Round the Result for a Clean Report

A summary dashboard needs a whole number instead of a long decimal.

=ROUND(AVERAGE(monthly_sales), 0)   // AVERAGE finds the raw mean, ROUND drops the decimals

Average While Ignoring Errors in the Range

Part of your dataset pulls in occasional #N/A errors from broken lookups elsewhere in the sheet, and a plain AVERAGE would fail entirely.

=AGGREGATE(1, 6, sales_data)

// 1 = the function code for AVERAGE
// 6 = ignore error values
// sales_data = the range containing occasional #N/A errors

Handling Errors

AVERAGE returns #DIV/0! when it can't find a single valid number to average. It also propagates any existing error, so if one cell in the range already holds #N/A or #REF!, AVERAGE returns that same error instead of calculating anything.

Common causes of #DIV/0!:

The range holds only blanks or text, with no numbers in it at all
You pointed the formula at a completely empty range
Every argument evaluates to a non-numeric result

=IFERROR(AVERAGE(sales_data), "No numeric data")
=AGGREGATE(1, 6, sales_data)   // averages sales_data while skipping any error values inside it

✓

If your range might contain error values from broken lookups elsewhere in the sheet, use AGGREGATE with function code 1 and option 6 instead of wrapping the whole formula in IFERROR. AGGREGATE averages the valid numbers and skips the errors, rather than discarding the entire result.

Notes & Gotchas

How do I calculate the average of a group of numbers in Excel?

Select an empty cell, type =AVERAGE(, then select the range of cells you want to average and close the parenthesis. For example, =AVERAGE(B2:B10) adds every numeric value in B2:B10 and divides by however many it found. You can also select the range first and read the average directly off the status bar at the bottom right of the window, no formula required.

What is the difference between the AVERAGE function and the AVERAGEIF function?

AVERAGE calculates the mean of every number in the range you give it, with no conditions attached. AVERAGEIF adds exactly one condition, so it only averages the numbers that meet a specific criterion, like averaging sales figures for the "West" region only. Use AVERAGE when you want the mean of everything; switch to AVERAGEIF the moment you need to filter by a condition first.

Does the AVERAGE function ignore blank or empty cells?

Yes. AVERAGE excludes blank cells from both the sum and the count, so a range with three numbers and two blanks divides by 3, not 5. It does not ignore zeros: a cell containing 0 counts as a real value and lowers the result accordingly. This distinction between "blank" and "zero" trips up more people than any other AVERAGE behavior.

When should I use MEDIAN instead of AVERAGE?

Switch to MEDIAN when your data has outliers, since one unusually high or low number can drag AVERAGE off center but barely moves the median. A salary list with one executive earning ten times everyone else is the classic case: AVERAGE overstates typical pay, MEDIAN doesn't.

Does AVERAGE include hidden or filtered rows?

Yes. AVERAGE calculates using every value in the range whether you hide or filter the rows or not. If you need an average that respects an active filter, use SUBTOTAL(1, range) or AGGREGATE(1, 5, range) instead, both of which can exclude hidden rows depending on the option you set. This is a common source of confusion on filtered reports, where the visible data looks like it should match AVERAGE's result but doesn't.

Related Functions

Function	Use this when...
`AVERAGEIF`	You need the average of only the numbers that meet one condition, like sales for a specific region.
`AVERAGEIFS`	You need to average numbers that meet two or more conditions at once.
`MEDIAN`	Your data has outliers and you want the middle value instead of one skewed by extremes.
`AGGREGATE`	Your range might contain error values and you need the average calculated around them.
`SUBTOTAL`	You need an average that automatically excludes hidden or filtered-out rows.