Excel MEDIAN Function — Find the Middle Value (with Examples)

Contents

What does MEDIAN do?
Syntax
5 real examples
MEDIAN vs AVERAGE
Conditional MEDIAN
FAQ

What does MEDIAN do?

MEDIAN returns the middle value in a dataset when all values are arranged in order. If there is an even number of values, it returns the average of the two middle ones. Because MEDIAN looks at the middle of the distribution rather than the sum, it is not distorted by extreme values at either end.

This makes MEDIAN more representative than AVERAGE for skewed data. One CEO earning £5 million does not pull the median salary up the way it pulls the average. That is why median salary, median house price, and median household income are the standard figures used in official statistics and fair reporting.

Syntax

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

Argument	Description
number1 required	The first value or range. Usually a range like B2:B100.
[number2] ... optional	Additional ranges or values. Up to 255 arguments.

5 real examples

Example 1

Median salary in a team

=MEDIAN(B2:B50)

Half the team earns below this figure, half above. One high earner does not inflate the result like it would with AVERAGE.

Example 2

Compare median to mean to detect skew

=AVERAGE(B2:B50) - MEDIAN(B2:B50)

A large positive difference means high-end outliers are pulling the average up. A large negative difference means low-end outliers are pulling it down. Near zero means roughly symmetrical data.

Example 3

Median of non-contiguous ranges

=MEDIAN(B2:B12, B14:B24)

Skips row 13 (perhaps a header or outlier) and finds the median across both ranges.

Example 4

Flag employees above the median

=IF(B2 > MEDIAN($B$2:$B$50), "Above median", "Below median")

Lock the range with $ so it does not shift when the formula is copied down.

Example 5

Conditional MEDIAN (array formula)

=MEDIAN(IF(A2:A100="North", B2:B100))

Press Ctrl+Shift+Enter (not just Enter) to enter as an array formula. Returns the median for North region only.

MEDIAN vs AVERAGE — which to use?

Situation	Use	Why
Symmetric data (heights, test scores)	AVERAGE	Mean and median are similar anyway
Skewed data (salaries, house prices)	MEDIAN	Resistant to outliers
Reporting to stakeholders	Both	Shows both typical and pulled-by-outlier values
Statistical calculations	AVERAGE	Mean works better in most statistical formulas

Conditional MEDIAN — the workaround

Unlike AVERAGEIF and SUMIF, Excel has no MEDIANIF function. The workaround is an array formula using IF inside MEDIAN:

=MEDIAN(IF(A2:A100="North", B2:B100))

Enter with Ctrl+Shift+Enter. The IF returns an array of values for North rows and FALSE for all others. MEDIAN ignores the FALSE values and finds the median of the North values only.

FAQ

Does MEDIAN count empty cells?

No — MEDIAN ignores empty cells and text. Only numeric values are included in the calculation.

What is the median of an even number of values?

The average of the two middle values. The median of {1,2,3,4} is (2+3)/2 = 2.5.

Is median the same as the 50th percentile?

Yes. MEDIAN(range) gives the same result as PERCENTILE(range, 0.5). Median is just the conventional name for the 50th percentile.

Why does UK official data use median household income?

Because very high incomes at the top pull the mean upward significantly, making average income look higher than what most people actually earn. Median is more representative of the typical household.

Practise MEDIAN and statistical formulas

ExcelPro has statistical exercises in the Data Analyst and HR tracks. Free to start.

Try stat exercises →

Related formulas

AVERAGE PERCENTILE STDEV LARGE AVERAGEIF VAR

The Excel MEDIAN Functionexplained simply