Excel Average by Range: Formula & Examples

Formulas & functions · Updated November 22, 2026

To average a range of cells in Excel, use the AVERAGE function: =AVERAGE(A1:A10) averages all numeric values in cells A1 through A10. For conditional averages, use AVERAGEIF or AVERAGEIFS. This article explains the syntax, examples, common errors, and practical tips for averaging data by range in Excel.

Understanding the AVERAGE Function

The AVERAGE function calculates the arithmetic mean of a set of numbers. Its syntax is:

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

You can reference individual cells, ranges, or a combination. For example, with values in A2:A6, =AVERAGE(A2:A6) returns the mean of those numbers. If the range contains text, logical values, or empty cells, they are ignored. However, if a cell contains zero, it is included in the calculation.

How to Average a Range: Step-by-Step

  1. Click the cell where you want the result.
  2. Type =AVERAGE(.
  3. Select the range with your mouse or type it manually (e.g., B2:B20).
  4. Close the parenthesis and press Enter.

For example, if sales figures are in B2:B20, =AVERAGE(B2:B20) returns the average sales. On Mac, the steps are identical; the keyboard shortcut to insert a function is Fn+Shift+F3 (Windows: Shift+F3).

Using AVERAGEIF for Conditional Averages

When you need to average only cells that meet a condition, use AVERAGEIF. Syntax: =AVERAGEIF(range, criteria, [average_range]). For example, with product names in A2:A20 and quantities in B2:B20, to average quantities for “Widget”:

=AVERAGEIF(A2:A20, "Widget", B2:B20)

This returns the average of B cells where the corresponding A cell equals “Widget”. Criteria can be numbers, text, or expressions like “>10”. For multiple conditions, use AVERAGEIFS with the syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, ...).

How to Choose the Right Range

Selecting the correct range is crucial. Consider these factors:

  • Include headers? Usually no, because headers are text and ignored, but if they are numbers, they will skew the result.
  • Blank cells? They are ignored by AVERAGE, so including extra blank rows is safe.
  • Hidden rows? AVERAGE includes hidden rows; use SUBTOTAL(101, range) to exclude them.
  • Errors? If any cell in the range has an error like #DIV/0!, AVERAGE returns an error. Use AGGREGATE to ignore errors.

Common Problems and Solutions

Users often encounter these issues:

  • Result is #DIV/0! This happens when the range contains no numeric values. Check for text or empty cells.
  • Result is wrong because of hidden zeros. Zeros are counted; if you want to exclude them, use =AVERAGEIF(range, "<>0").
  • Average ignores text but counts numbers stored as text. Sometimes cells look like numbers but are text; use VALUE or convert them.
  • Average changes when you add data. Use a dynamic range like =AVERAGE(A2:A100) if you plan to add rows, or use an Excel Table.

Comparing AVERAGE with Other Functions

Excel offers several related functions:

  • MEDIAN: returns the middle value, less affected by outliers.
  • MODE: returns the most frequent value.
  • SUBTOTAL(1, range): averages visible rows only (when rows are filtered).
  • AGGREGATE(1, options, range): averages while ignoring errors and hidden rows, with more control.

For example, =MEDIAN(A2:A20) gives the median, which is useful when data has extreme values.

Alternatives for Dynamic Ranges

If your data range changes frequently, consider:

  • Excel Table: Convert your data to a table (Ctrl+T), then use structured references like =AVERAGE(Table1[Sales]). The range automatically expands.
  • OFFSET: =AVERAGE(OFFSET(A1,0,0,COUNTA(A:A),1)) averages a dynamic range based on count of non-empty cells.
  • FILTER (Excel 365): =AVERAGE(FILTER(B2:B100, A2:A100="Widget")) for more complex criteria.

Frequently Asked Questions

How do I average only visible cells after filtering?
Use =SUBTOTAL(101, A2:A100) instead of AVERAGE. The 101 function code ignores rows hidden by a filter.

Why does my average include zeros?
AVERAGE includes zeros because they are numeric. To exclude zeros, use =AVERAGEIF(A2:A100, "<>0").

Can I average a range with errors?
Yes, use =AGGREGATE(1, 6, A2:A100) where 6 tells it to ignore error values. This works in Excel 2010 and later.

What is the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text and logical values; AVERAGEA counts text as 0 and TRUE as 1. Use AVERAGE for numeric data.

How do I average the top 10 numbers in a range?
Combine with LARGE: =AVERAGE(LARGE(A2:A100, ROW(1:10))) entered as an array formula (Ctrl+Shift+Enter in older Excel; in Excel 365, just Enter).

Does AVERAGE work with dates?
Yes, dates are stored as numbers, so averaging them gives a date. For example, averaging dates yields the midpoint date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.