MEDIAN Function

Returns the middle number of a set.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Descriptive Statistics module

Overview

MEDIAN sorts the numbers and returns the one in the middle. With an even count, it averages the two middle numbers.

Unlike AVERAGE, one very large or very small value barely moves it — which is why salaries and house prices are usually reported as medians.

Syntax

=MEDIAN(number1, [number2], …)

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range.
number2, …NoUp to 255 more numbers, cells or ranges.

Examples

Example 1 — Middle sales result

Sorted, the sales are 39,800 · 48,500 · 48,500 · 50,000 · 55,400 · 62,000 · 71,250. The 4th of 7 is the middle.

E2fx=MEDIAN(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Median50000
3BenSouth48500Average53635.71
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=MEDIAN(C2:C8) Explain

Example 2 — Even number of values

The two middle values (2 and 3) are averaged.

=MEDIAN(1,2,3,4) Explain

Returns 2.5.

Example 3 — Resistant to outliers

One huge order changes the average a lot, the median hardly at all.

=MEDIAN(C2:C8,1000000) Explain

Returns 52,700 — while the average jumps to about 171,931.

Common mistakes

  • Text and blanks are skipped, zeros aren't. A 0 standing in for missing data shifts the median down.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.