AVERAGE

Calculate the arithmetic mean of numbers in a range or list.

Statistical
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=AVERAGE(number1, number2, ...) Returns: Number

Arguments

Argument Required Description
number1 Yes The first number for which you want to calculate the average.
number2 No The second number for which you want to calculate the average.
... No More numbers for which you want to calculate the average.

About

AVERAGE finds the mean of numbers by adding them up and dividing by the count. Use it when you need to find the typical value in a dataset, like average sales per month, mean test scores, or typical response times.

The function only counts cells with numbers. Empty cells don't affect the calculation, but cells with zero do count. Text, logical values like TRUE or FALSE, and text that looks like numbers get ignored. If you need to include logical values in your average, try AVERAGEA instead.

For more targeted analysis, check out AVERAGEIF when you want to average only values meeting a condition, or AVERAGEIFS for multiple conditions.

Examples

Zero versus empty cells

See how zeros count but empty cells don't in this sales tracker. February shows zero sales while April is blank. Change February from 0 to empty and watch the average jump from 1350 to 1800.

Spreadsheet editor

Text that looks like numbers

Watch what happens when ratings get stored as text. Two ratings have apostrophes making them text, so AVERAGE ignores them. Delete the apostrophes in cells B3 and B5 to see the average change from 91.67 to 90.2.

Spreadsheet editor

Multiple ranges in one formula

Use AVERAGE with multiple ranges to calculate across non-adjacent columns. This exam tracker averages nine scores from three separate columns. Try changing any score to see the overall average update.

Spreadsheet editor

Watch out for

⚠

Zero versus empty cells

Cells containing zero are included in the average, but empty cells are not. This can skew results if you're using zeros as placeholders.

→ Use empty cells for missing data, not zeros. If you have legitimate zero values mixed with missing data, consider using a helper column to mark which zeros are real.

⚠

Text that looks like numbers

Numbers stored as text (like '100 instead of 100) get ignored by AVERAGE, giving you incorrect results without any error message.

→ Check that your data is formatted as numbers, not text. You can multiply text numbers by 1 or use the VALUE function to convert them first.

⚠

Error values break the calculation

If any cell in your range contains an error like #DIV/0! or #N/A, AVERAGE returns an error instead of skipping it.

→ Wrap your formula in IFERROR like =IFERROR(AVERAGE(A1:A10), 0) or use AGGREGATE which can ignore errors.

⚠

Mixing conditions with AVERAGE

AVERAGE doesn't support conditions, so you can't easily average only values above a threshold or matching specific criteria.

→ Use AVERAGEIF for single conditions or AVERAGEIFS for multiple criteria instead of AVERAGE.

Tips & notes

AVERAGE accepts up to 255 individual arguments, which can be numbers, cell references, or ranges. Logical values and text typed directly as arguments get ignored, but they also don't cause errors. The function calculates the arithmetic mean, not the median or mode. For the middle value in a dataset, use MEDIAN.

Common questions

What's the difference between AVERAGE and AVERAGEA?

AVERAGE only counts numbers and skips everything else. AVERAGEA counts numbers plus logical values (TRUE = 1, FALSE = 0) and text (counts as 0). Use AVERAGE for numeric data and AVERAGEA only if you specifically need to include logical values in your calculation.

How do I average only positive numbers or values above a certain threshold?

Use AVERAGEIF instead of AVERAGE. For example, =AVERAGEIF(A1:A10, ">0") averages only positive values, and =AVERAGEIF(A1:A10, ">100") averages only values greater than 100.

Can AVERAGE work with time values?

Yes, since Excel stores times as decimal numbers (like 0.5 for 12:00 PM). Use =AVERAGE(A1:A10) on time values to get the mean time. Format the result cell as Time to display it properly.

Why is my AVERAGE result different from what I calculated manually?

Check for hidden rows, cells with zero (which count), or text that looks like numbers (which don't count). Also verify that empty cells aren't being counted. AVERAGE only divides by the count of numeric cells, not all cells in the range.

Practice this function

Calculate Average Sales

Beginner

Find the average order value from a list of transactions.

Start →

Using Average

Beginner

Start →