MEDIAN in Excel: The Middle Value (and When to Use It)
How MEDIAN works in Excel, why it beats AVERAGE when data has outliers, median with conditions, and MODE for the most common value.
By Ayoub Farid · · 1 min read
MEDIAN returns the middle value of a list. Half the values are above it, half below. When a few extreme values would distort an average, the median shows what's typical.
Syntax
=MEDIAN(number1, [number2], ...)Example: why it matters
Delivery times (days): 2, 3, 3, 4, 21.
=AVERAGE(...)→ 6.6 days=MEDIAN(...)→ 3 days
One very late delivery makes the average look terrible. The median shows what most customers experienced.
With an even number of values, MEDIAN averages the two middle ones: 2, 3, 5, 8 → 4.
When to use which
| Use AVERAGE | Use MEDIAN |
|---|---|
| Totals matter (budget per month) | Typical case matters |
| Values are evenly spread | A few extreme values |
| Scores, temperatures | Salaries, prices, times |
Reporting both is often the most honest choice.
Median with a condition
There's no MEDIANIF, but in Microsoft 365 and Google Sheets:
=MEDIAN(FILTER(B2:B200,C2:C200="North"))In older Excel, enter =MEDIAN(IF(C2:C200="North",B2:B200)) as an array formula (Ctrl+Shift+Enter).
Most common value: MODE
=MODE.SNGL(B2:B200)Returns the value that appears most often (numbers only).
Frequently asked questions
What is the median in Excel?
The middle value of a sorted list, returned by =MEDIAN(range).
What's the difference between mean and median?
The mean (AVERAGE) adds and divides; the median is the middle value and isn't pulled by outliers.
Does MEDIAN ignore blank cells?
Yes. Like AVERAGE, it ignores blanks and text but includes zeros.
How do I find the most frequent number?
Use =MODE.SNGL(range).