AVERAGE and MEDIAN functions in Excel

AVERAGE gives the arithmetic mean of a set of numbers; MEDIAN gives the middle value.

The average is the most quoted summary of a set of numbers: add them up and divide by how many there are. AVERAGE does both steps. What needs care is not the arithmetic but the question of which cells get counted.

Syntax

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

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

Argument	What it is
`number1, number2, ...`	Numbers, cells or ranges.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=AVERAGE(C2:C9)
Result: 66.875
The average Maths mark of the eight students.
=AVERAGE(D2:D9)
Result: 70
Science has one empty cell. It is left out, so the total is divided by 7, not 8.
=SUM(D2:D9)/8
Result: 61.25
Treating the missing mark as zero gives a lower figure. Decide which one you mean.
=AVERAGE(C2:E2)
Result: 84.66666667
Across a row: Asha's average over three subjects.
=ROUND(AVERAGE(C2:E9),1)
Result: 68.5
A block of cells, rounded to one decimal place.
=MEDIAN(C2:C9)
Result: 72.5
The middle Maths mark. With eight values, it is halfway between the 4th and 5th in order.
=AVERAGE(A2:A9)
Result: #DIV/0!
A range with no numbers at all: #DIV/0!.

What AVERAGE counts

AVERAGE adds up the numbers in the range and divides by the number of cells that contain numbers.

Empty cells are left out. They do not count as zero.
Text is left out. A cell with "absent" or "n/a" is ignored.
Zeros are counted. A typed 0 is a number.

The second and third examples show why this matters. Tara has no Science mark. AVERAGE divides by 7. If a missing mark ought to count as zero, either type the zero or divide the SUM by the full number of students.

This is the single most important thing to understand about averages in a spreadsheet. An empty cell and a zero look similar and mean different things.

Average or median?

The average is pulled about by extreme values. If seven people earn 30,000 a month and one earns 5,00,000, the average is close to 89,000, a figure that describes none of them. The median, the middle value when the numbers are put in order, stays at 30,000.

Use the median when a few very large or very small values would give a false picture: incomes, house prices, waiting times. With an even number of values, MEDIAN returns the mean of the two middle ones.

There is also MODE.SNGL, which returns the value that occurs most often.

Averages of averages

Do not average a column of averages unless every group is the same size. A class of 10 with an average of 80 and a class of 40 with an average of 60 do not average 70 between them. The true figure is 64, because the second class has four times as many students. Go back to the original numbers, or weight the averages with SUMPRODUCT.

Averaging some rows only

For the average of the rows that meet a condition, such as the marks of one class, use AVERAGEIF and AVERAGEIFS.

To average the largest few values, combine with LARGE: =AVERAGE(LARGE(C2:C9,{1,2,3})) is the mean of the top three.

Errors

#DIV/0! means there was not a single number in the range, so there is nothing to divide by. Wrap the formula in IFERROR, or check with COUNT first.
Any error value in the range makes AVERAGE return that error. =AGGREGATE(1,6,range) averages while ignoring errors.
Numbers stored as text are skipped, which changes both the total and the count.

Display

Averages usually have long decimal tails. Use ROUND when the rounded figure will be used further, or the cell format when only the display matters.

In Google Sheets

AVERAGE and MEDIAN work the same way in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.