Excel AVERAGE Function — How to Use It (with Examples)

Contents

What does AVERAGE do?
Syntax
6 real examples
AVERAGEIF — conditional average
AVERAGE vs MEDIAN
Common mistakes
FAQ

What does AVERAGE do?

AVERAGE calculates the arithmetic mean — it adds all the numbers in a range and divides by how many there are. It gives you the typical value in a dataset, which is more meaningful than just the total when you want to understand what is normal.

Use AVERAGE when you need to answer questions like: what is our typical monthly revenue? What was the average score across all students? What does a customer normally spend?

Syntax

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

Argument	Description
number1 required	The first number, cell, or range to average. Usually a range like B2:B12.
number2 ... optional	Additional numbers or ranges. Up to 255 arguments accepted.

6 real examples

Example 1

Average monthly sales

=AVERAGE(B2:B13)

Returns the mean of all 12 monthly values. Empty cells are ignored — only numeric values are included.

Example 2

Average of non-adjacent ranges

=AVERAGE(B2:B4, B8:B10)

Averages Q1 and Q3 only, skipping Q2 and Q4 entirely.

Example 3

Average above a threshold

To average only values above 1000, use AVERAGEIF:

=AVERAGEIF(B2:B100, ">1000")

Example 4

Compare to the overall average

=IF(B2 > AVERAGE(B$2:B$13), "Above avg", "Below avg")

Lock the range with $ so it doesn't shift when copied down. Each row gets labelled relative to the overall mean.

Example 5

Exclude zeros from the average

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

Zeros can distort an average significantly. This formula excludes them, averaging only non-zero values.

Example 6

Rolling 3-month average

=AVERAGE(B2:B4) ← copy down; each row averages 3 months

A rolling average smooths out spikes and shows underlying trends more clearly than raw monthly data.

AVERAGEIF — conditional average

AVERAGEIF averages only the values where a condition is met.

=AVERAGEIF(range, criteria, [average_range])

AVERAGEIF Example

Average sales for the North region only

=AVERAGEIF(A2:A100, "North", B2:B100)

For multiple conditions use AVERAGEIFS:

=AVERAGEIFS(C2:C100, A2:A100, "North", B2:B100, "Coffee")

AVERAGE vs MEDIAN — which to use?

AVERAGE is pulled by extreme outliers. One very high or very low value shifts the mean significantly. MEDIAN returns the middle value when all numbers are sorted — it is resistant to outliers.

💡 Rule of thumb

Use AVERAGE when your data is roughly symmetrical. Use MEDIAN when you have outliers — for example house prices, salaries, or waiting times — where one extreme value would distort the average.

Common mistakes

⚠️ Empty cells vs zeros

AVERAGE ignores empty cells but includes zeros. If a zero means "no data" in your dataset, use AVERAGEIF to exclude zeros: =AVERAGEIF(B2:B100,"<>0").

⚠️ Text values silently excluded

AVERAGE skips cells containing text without warning. If some of your "numbers" are stored as text, they won't be included. Use ISNUMBER to check, or AVERAGEA to include text values as zero.

FAQ

What is the difference between AVERAGE and AVERAGEA?

AVERAGE ignores text and logical values (TRUE/FALSE). AVERAGEA includes them — treating text as 0 and TRUE as 1, FALSE as 0. Use AVERAGE for numeric data; AVERAGEA is rarely needed.

Can AVERAGE handle errors in the range?

No — if any cell in the range contains an error like #N/A or #DIV/0!, AVERAGE returns that error. Use AGGREGATE(1,6,B2:B100) to average while ignoring errors.

How do I calculate a weighted average?

Use SUMPRODUCT divided by SUM: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10), where B is the values and C is the weights.

Is AVERAGE the same as MEAN?

Yes. In Excel, AVERAGE calculates the arithmetic mean — the sum divided by the count. There is no separate MEAN function.

Practise AVERAGE with real data

ExcelPro has AVERAGE and AVERAGEIF exercises in every track — realistic business scenarios, instant feedback, free to start.

Try AVERAGE exercises →

Related formulas

SUM MEDIAN SUMIF COUNTIF STDEV PERCENTILE

The Excel AVERAGE Functionexplained simply