MEDIAN Function in Excel: The Middle Value

📎 This article includes 1 downloadable practice file ↓

Excel Core FunctionsLevel: Foundational → IntermediateCompatibility: All Excel Versions

In this article

1. Syntax & Parameter Breakdown
2. Where You Will Use It
3. Practical Worked Examples
4. Mistakes People Make & How to Avoid Them
5. Frequently Asked Questions
6. When should you use MEDIAN instead of AVERAGE?
7. How does MEDIAN handle an even number of values?
8. Can MEDIAN be calculated with conditions (like a MEDIANIF)?
9. MEDIAN in practice
10. Where you will use it
11. Worked example
12. Mistakes people make
13. Continue learning

The MEDIAN function in Excel is an essential spreadsheet tool designed to Calculate the statistical median (exact middle value) of a numeric dataset, providing a realistic baseline unaffected by extreme outliers.

Syntax & Parameter Breakdown

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

Argument	Requirement & Description
number1	Required. The first number, cell reference, or range of numbers.
number2, ...	Optional. Up to 254 additional numeric items or ranges.

Where You Will Use It

Standard metric for salaries, home prices, customer response times, and financial benchmarks where a few ultra-high values skew the mean average.

Practical Worked Examples

Here are common real-world formula scenarios utilizing MEDIAN:

Formula	Expected Behavior & Purpose
=MEDIAN(B2:B200)	Returns the median salary from column B, giving a true picture of typical pay.
=MEDIAN(A2:A50, C2:C50)	Calculates the median across two separate data sets.
=MEDIAN(10, 20, 30, 40)	Returns 25 (the average of the two middle numbers in an even-sized set).

Mistakes People Make & How to Avoid Them

Non-numeric cells, text strings, and blank cells in ranges are ignored. If all values are non-numeric, MEDIAN returns a #NUM! error.

Frequently Asked Questions

When should you use MEDIAN instead of AVERAGE?

Use MEDIAN whenever your dataset has extreme outliers (e.g. real estate pricing or compensation) that distort the arithmetic mean.

How does MEDIAN handle an even number of values?

If the count is even, MEDIAN calculates the average of the two numbers in the middle.

Can MEDIAN be calculated with conditions (like a MEDIANIF)?

Yes, in Excel 365 use =MEDIAN(FILTER(B2:B100, A2:A100=”Engineering”)).

📚 Master spreadsheet formulas with our Free Excel Course or explore related functions in the Formula Lab.

MEDIAN in practice

Where you will use it

• Typical salary without big outliers
• Middle delivery time
• Typical order value when a few orders are huge

Worked example

Notice how one Rs 95,000 order barely moves the median.

Order	Formula	Result
1200	=MEDIAN($A$2:A2)	1200
1500	=MEDIAN($A$2:A3)	1350
1800	=MEDIAN($A$2:A4)	1500
95000	=MEDIAN($A$2:A5)	1650

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Using AVERAGE with outliers	MEDIAN resists extreme values
Even number of values	MEDIAN averages the two middle values

Continue learning

All Statistical guides →

GAMMAINV Function in Excel: Inverse Gamma (Older Name)GAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel…GAMMADIST Function in Excel: Gamma Distribution (Older Name)GAMMADIST returns the gamma probability. It is a compatibility function: Excel keeps it so…TTEST Function in Excel: t-Test (Older Name)TTEST returns the p-value of a t-test between two samples. It is a compatibility…GAMMA.INV Function in Excel: Value for a Gamma ProbabilityStatisticsLevel: ExpertAvailable in: Excel 2010+GAMMA.INV returns the value at a given gamma probability.Syntax=GAMMA.INV(probability, alpha.

📎 Practice files for this article

• 📗
  Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.