MEDIAN Function in Excel
Returns the middle value of a dataset. When one CEO earns $500K and nine analysts earn $50K, MEDIAN tells you the truth AVERAGE hides. This is your outlier-resistant weapon.
The Outlier-Resistance Advantage
Same 10 salaries. Two functions. Two very different stories.
$50,000 each and 1 CEO earning $500,000. What is a "typical" salary here?
Quick Answer
MEDIAN returns the middle value in a list of numbers. If the count is odd, it returns the single middle value. If the count is even, it returns the average of the two middle values.
Example: =MEDIAN(A2:A20) returns the middle value of the range A2:A20. Excel sorts internally — you don't need to sort first.
📊 MEDIAN Practice Workbook
All 5 examples below, ready to open in Excel. Salary skew case study included.
Syntax
MEDIAN accepts between 1 and 255 arguments. Each argument can be a single number, a cell reference, or a range.
| Argument | Type | Description |
|---|---|---|
| number1 | Required | The first number, cell reference, or range for which you want the median. |
| number2, ... | Optional | Up to 254 additional numbers, references, or ranges. In practice, one range covers most cases. |
What MEDIAN ignores: empty cells, text values, and logical values (TRUE/FALSE) inside a range reference. What it counts: the number zero — it's a valid number and gets included.
5 Worked Examples
Median of an odd count (7 values)
Seven exam scores. Excel sorts internally, then picks the middle.
| Student | Score |
|---|---|
| Emma | 62 |
| David | 71 |
| Michael | 78 |
| Sofia | 82 |
| Aisha | 85 |
| James | 91 |
| Priya | 96 |
Result: 82 — the 4th value out of 7 when sorted.
Median of an even count (6 values)
Six house prices. No single middle exists, so Excel averages the two middle values.
| Property | Price |
|---|---|
| House A | $180,000 |
| House B | $210,000 |
| House C | $245,000 |
| House D | $275,000 |
| House E | $310,000 |
| House F | $390,000 |
Result: $260,000 — the average of $245,000 and $275,000 (the two middle values).
Salary skew — real estate case study
A firm reports "average salary $95K." True — but misleading. The median tells the honest story.
| Role | Salary |
|---|---|
| Analyst 1 | $50,000 |
| Analyst 2 | $50,000 |
| Analyst 3 | $50,000 |
| Analyst 4 | $50,000 |
| Analyst 5 | $50,000 |
| Analyst 6 | $50,000 |
| Analyst 7 | $50,000 |
| Analyst 8 | $50,000 |
| Analyst 9 | $50,000 |
| CEO | $500,000 |
MEDIAN: $50,000 reflects what most employees actually earn. AVERAGE: $95,000 is pulled up by a single outlier. When someone says "average," ask: median or mean?
MEDIAN ignores text and blanks
Mixed data in the range. MEDIAN silently skips text and empty cells — no error, just cleaner math.
| Cell | Value | Treated as |
|---|---|---|
| B2 | 10 | Number |
| B3 | 20 | Number |
| B4 | "N/A" | Ignored |
| B5 | 30 | Number |
| B6 | (blank) | Ignored |
| B7 | 40 | Number |
| B8 | 50 | Number |
Result: 30 — MEDIAN evaluates only {10, 20, 30, 40, 50}. Middle of 5 sorted values = 30.
Multiple ranges combined
MEDIAN can take several ranges at once. Combine two teams' scores into one median.
Excel merges both ranges into one pool, sorts internally, and returns the middle value. Same math as if you pasted both columns into one.
How MEDIAN Handles Odd vs Even Counts
Two rules, one function. This is the whole trick.
Odd Count → Single Middle
Sorted: 3, 7, 11, 15, 22
position (n+1)/2 = 3rd value
Even Count → Average of Two Middles
Sorted: 4, 8, 12, 16, 20, 26
(12 + 16) / 2 = 14
Interactive Playground
Your Data (10 values)
A3 → 58
A4 → 65
A5 → 71
A6 → 78
A7 → 84
A8 → 89
A9 → 92
A10 → 95
A11 → 2,400 ← outlier
Two Formulas, Two Answers
Average of the two middles (78 & 84). Outlier ignored.
Wrecked by the single outlier of 2,400.
The Basic Aggregates Family
Four functions that describe a dataset in a single number. Learn one, understand all four.
⚠ MEDIANIFS Doesn't Exist in Excel
Excel has SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS — but no MEDIANIFS. Never has, still doesn't as of 2026. If you need a median filtered by criteria (e.g. median salary of just the Analyst role), you need a workaround.
=MEDIAN(IF(Role="Analyst", Salary))
In Excel 2016 and earlier, press Ctrl+Shift+Enter instead of Enter to make it array-evaluate. In Excel 365 it works with a normal Enter — the IF spills naturally.
=MEDIAN(FILTER(Salary, Role="Analyst"))
Cleaner syntax. FILTER returns only the matching salaries; MEDIAN takes the middle. Only works in Excel 365 / 2021+.
MEDIAN is Just QUARTILE 2 (in disguise)
Median splits data in half. Quartiles split it in quarters. The 2nd quartile IS the median.
| What it returns | Formula | Position |
|---|---|---|
| Smallest value | =QUARTILE(range, 0) | 0% (min) |
| Bottom quartile boundary | =QUARTILE(range, 1) | 25% |
| Middle value (median) | =QUARTILE(range, 2) = =MEDIAN(range) | 50% |
| Top quartile boundary | =QUARTILE(range, 3) | 75% |
| Largest value | =QUARTILE(range, 4) | 100% (max) |
Use MEDIAN when you want the middle only. Use QUARTILE when you're building a box plot or a five-number summary of the whole distribution.
Common Errors
| Error | Cause | Fix |
|---|---|---|
| #NUM! | Range contains no numeric values (only text, blanks, or logical values). | Add at least one number, or use IFERROR to catch: =IFERROR(MEDIAN(range), 0). |
| #VALUE! | A directly-entered argument is text like =MEDIAN("hello", 5). (Text inside a range reference is silently ignored — this only fires when you type text as a literal argument.) |
Remove the text argument or wrap it in VALUE() if it's a numeric string. |
| #NAME? | Function name typo: =MEDAIN or =MEDIAN( with a missing closing bracket. |
Check spelling. Excel auto-suggests as you type — accept the suggestion with Tab. |
| Wrong answer | You expected MEDIAN to filter by criteria — it doesn't. MEDIAN of the entire range gives a different number than median of a subset. | Use the array-formula workaround: =MEDIAN(IF(criteria, range)). |
📊 Practice What You Just Read
Every example, every workaround, all in one workbook. Change the values, watch MEDIAN respond.
Related Functions
Compatibility
| Platform | Supported | Version | Notes |
|---|---|---|---|
| Excel for Windows | ✓ | 1.0+ (1985) | All versions |
| Excel for Mac | ✓ | 1.0+ | All versions |
| Microsoft 365 | ✓ | Current | Full support |
| Excel Online | ✓ | Current | Full support |
| Excel Mobile (iOS/Android) | ✓ | Current | Full support |
| Google Sheets | ✓ | All | Identical behavior |
| LibreOffice Calc | ✓ | All | Identical behavior |
How to Use MEDIAN — Step by Step
- Click an empty cell where you want the median to appear.
- Type
=MEDIAN(— Excel will auto-suggest the function name as soon as you type "MED". - Select the range of numbers with your mouse, or type it manually: e.g.
B2:B20. - Close the parenthesis with
)and press Enter. - Verify with AVERAGE. In an adjacent cell, type
=AVERAGE(and select the same range. A big gap between median and mean signals outliers are present. - If you need a filtered median, use the array-formula workaround:
=MEDIAN(IF(criteria_range=criterion, value_range)). Press Ctrl+Shift+Enter in Excel 2016 or earlier.
Explore More Functions
📊 One More Time — Grab the Workbook
All 5 MEDIAN examples plus AVERAGE, MIN, MAX. Free forever.
Frequently Asked Questions
When should I use MEDIAN instead of AVERAGE?
Use MEDIAN whenever your data contains outliers, is skewed, or represents income, house prices, response times, or anything where a few extreme values can distort the mean. AVERAGE is fine for uniform data (test scores from a normal distribution, daily temperatures) but misleading for skewed data.
A good habit: compute both and compare. If they're close, either is fine. If the mean is much higher than the median, outliers are pulling the mean up. If the mean is much lower, outliers are pulling it down.
Does MEDIAN work with non-numeric data?
Inside a range reference, MEDIAN silently ignores text and empty cells — no error, it just skips them. This is usually what you want.
However, if you pass text as a direct literal argument (e.g. =MEDIAN("hello", 5, 10)), MEDIAN returns #VALUE!. And logical values (TRUE/FALSE) inside a range are also ignored, but if entered directly they get treated as 1 and 0 respectively.
Is there a MEDIANIFS function?
No. Excel has SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS — but MEDIANIFS has never existed and still doesn't in 2026. This is one of Excel's most-requested missing functions.
Workaround: =MEDIAN(IF(criteria_range=criterion, value_range)). In Excel 2016 and earlier, enter this with Ctrl+Shift+Enter. In Excel 365, plain Enter works. Or use the modern FILTER: =MEDIAN(FILTER(value_range, criteria_range=criterion)).
How does MEDIAN handle even-numbered datasets?
When there's an even count of values, no single "middle" exists. MEDIAN averages the two middle values instead.
Example: for the sorted list {10, 20, 30, 40}, the two middle values are 20 and 30. MEDIAN returns (20 + 30) / 2 = 25. For {12, 16}, MEDIAN returns 14.
Does MEDIAN count zeros?
Yes. Zero is a valid number and gets included in the median calculation. This is different from empty cells — zeros count, blanks don't.
If you want to exclude zeros (e.g. median sales excluding closed days), use: =MEDIAN(IF(range<>0, range)) as an array formula.
Can MEDIAN handle dates?
Yes — Excel stores dates as numbers (serial numbers counting days since 1900-01-01), so MEDIAN works on date ranges naturally. The result is a number that Excel displays as a date if the cell is formatted as one.
Useful for finding the "typical" date in a set of events: e.g. median order date across a quarter.
What's the difference between MEDIAN and PERCENTILE 50?
They're the same in intent — both return the value that splits the dataset in half. However, they can differ by one interpolation method in edge cases.
=MEDIAN(range) uses linear interpolation for even counts (averages the two middle values). =PERCENTILE(range, 0.5) also uses linear interpolation, so for most datasets they give identical results. Prefer MEDIAN for clarity — it's what everyone recognizes.
Does MEDIAN require sorted data?
No. Excel sorts the values internally before finding the middle. You can pass an unsorted range and get the correct answer instantly.
This is different from LOOKUP or MATCH with approximate match, which do require sorted input.
What if my range contains #N/A or other errors?
MEDIAN propagates errors — if any cell in the range contains an error like #N/A, #DIV/0!, or #REF!, MEDIAN returns that error too.
Fix with IFERROR at the source (better) or wrap MEDIAN itself: =MEDIAN(IFERROR(range, "")) as an array formula.
How is MEDIAN calculated for very large datasets?
Excel uses an efficient sorting algorithm internally. Performance is excellent for datasets up to hundreds of thousands of rows — MEDIAN across 100,000 cells calculates in milliseconds.
For millions of rows or repeated calculations, consider Power Query or a Power Pivot median measure using DAX's MEDIAN function, which is optimized for the columnar engine.
Can I use MEDIAN in conditional formatting?
Yes. A common pattern: highlight cells above the median with =A2>MEDIAN($A$2:$A$100). This flags "above-typical" values visually — great for spotting outliers or top performers at a glance.
Note the mix of relative (A2) and absolute ($A$2:$A$100) references — this pattern applies to any conditional format that compares each row to a whole-column statistic.
Is MEDIAN the "50th percentile"?
Yes — that's exactly what it means. The median is the value where 50% of the data falls below and 50% falls above.
In statistics-heavy contexts, you'll often see the median labeled as P50 or the "second quartile" (Q2). Excel has separate functions for these — PERCENTILE(range, 0.5) and QUARTILE(range, 2) — but all three return the same value for the same input.
Templates That Use MEDIAN
Salary Analysis Template
Compare median vs mean salary by role, department, and location. Detect pay-gap outliers instantly.
USES MEDIANSales Dashboard
Median deal size beats average when a few whales skew the picture. Built-in outlier detection.
USES MEDIANKPI Dashboard
Median response time, median satisfaction score, median delivery days — the metrics that don't lie.
USES MEDIANBudget Tracker
Median monthly spend by category. See what your "typical" month actually looks like.
USES MEDIANHR Analytics
Median tenure, median performance rating, median time-to-hire. Central-tendency metrics for people ops.
USES MEDIANReal Estate Comps
Median home price is the industry standard — mansions and tear-downs shouldn't skew your comparables.
USES MEDIANCustomer Survey Analysis
Median rating per question. Robust to a few angry customers or overzealous fans.
USES MEDIANInventory Tracker
Median days-on-hand per SKU. Reveal typical stock behavior without one seasonal spike distorting it.
Never Compute MEDIAN Wrong Again
Excel Wizard sits inside Excel and writes MEDIAN — with criteria filters, array workarounds, and outlier detection — from a plain-English prompt. AI-powered formulas, right in your ribbon.
Get Excel Wizard Add-in →