Skip to content

Excel function · Statistics

Excel AVERAGE function

AVERAGE adds up the numbers you give it and divides by how many there are: average monthly sales, a class's average score, the average delivery time.

In Turkish Excel: ORTALAMA

What it does

AVERAGE takes one or more ranges, adds up the numbers in them and divides by the count. =AVERAGE(B2:B13) returns the average of twelve months. The result equals SUM divided by COUNT.

Empty cells and text are left out, but 0 is included. The difference matters: type 0 for a month with no sales and the average drops; leave it empty and that month is treated as if it did not exist. Which one is right depends on your question, so fill the list accordingly.

For averages with conditions use AVERAGEIF and AVERAGEIFS. If a few very large or very small values pull the average, MEDIAN, which returns the middle value, can be a better summary. For an average weighted by quantity you need SUMPRODUCT.

Syntax

English Excel=AVERAGE(number1, [number2], ...)
Turkish Excel=ORTALAMA(sayı1; [sayı2]; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
number1RequiredThe first number, cell or range to average.
number2OptionalOptional further numbers, cells or ranges, up to 255 arguments.

Examples

1Each rep's three-month average

The formula in E2 is filled down and averages the three months on each row.

E2fx=AVERAGE(B2:D2)
Each rep's three-month average: example table
ABCDE
1RepJanFebMarAverage
2Emma48,50052,30050,10050,300.0
3Liam31,20029,75035,40032,116.7
4Olivia61,00058,25064,70061,316.7
5Noah22,80027,10024,95024,950.0
  • E2 (filled down to E5)=AVERAGE(B2:D2)

Emma's average is 50,300.0 and Olivia's is 61,316.7.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Empty cell versus zero

Liam made no sales in March and the cell holds 0. Olivia's March figure has not arrived yet, so her cell is empty. Both rows have the same January and February figures.

E2fx=AVERAGE(B2:D2)
Empty cell versus zero: example table
ABCDE
1RepJanFebMarAverage
2Liam30,00036,000022,000.0
3Olivia30,00036,00033,000.0
  • E2 (filled down to E3)=AVERAGE(B2:D2)

Liam's average is divided by three months: 22,000.0. Olivia's is divided by two: 33,000.0. An empty cell is left out; a 0 is counted.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Flag values above the average

Column C compares each store's sales with the average of all stores. The average range is locked with $, so it does not shift when the formula is filled down.

C2fx=IF(B2>=AVERAGE($B$2:$B$6),"Above average","Below average")
Flag values above the average: example table
ABC
1StoreSalesStatus
2Camden184,000Above average
3Leeds126,500Below average
4York141,200Below average
5Bristol203,750Above average
6Hull98,400Below average
  • C2 (filled down to C6)=IF(B2>=AVERAGE($B$2:$B$6),"Above average","Below average")

Camden and Bristol show Above average; the other three stores show Below average.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#DIV/0!

Cause: There are no numbers in the range: the cells are empty or the numbers are stored as text.

Fix: Check the range and how the numbers are stored; to show text instead of an error for an empty range, use =IFERROR(AVERAGE(B2:B13),"No data").

Average too low

Cause: Periods without data were filled with 0, and AVERAGE counts those zeros.

Fix: Leave cells without data empty, or use =AVERAGEIF(B2:B13,"<>0").

Average of too few values

Cause: Numbers stored as text are not included, so the average is based on fewer values.

Fix: Convert those cells with VALUE or with Convert to Number from the warning icon.

Good to know

  • To see an average without a formula, select the cells; the status bar shows it.
  • If later calculations should use the rounded average, use =ROUND(AVERAGE(B2:B13),2); for appearance only, cell formatting is enough.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add an Average column with each rep's average sales over the three months

Convert to table

Bring your sales table into the tool and type this. Nexell adds the Average column with real Excel formulas and shows it in the preview first; in the downloaded file, the average follows any change in a month's sales.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I average in Excel excluding zeros?

=AVERAGEIF(B2:B13,"<>0") averages the values that are not zero. Empty cells are already left out of an average.

What is the difference between average and median?

The average divides the total by the count, so a single very large value pulls it up. MEDIAN returns the middle value of the sorted list and is not affected by extremes.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free