How to Use MEDIAN Function in Excel

Key Insights

• MEDIAN returns the middle value in a dataset and handles outliers better than AVERAGE, making it essential for analyzing salaries, real estate prices, and any skewed data distribution.
• Excel’s MEDIAN function ignores text and empty cells automatically, but you can combine it with IF or FILTER functions to calculate conditional medians based on specific criteria.
• For datasets with even numbers of values, MEDIAN automatically averages the two middle numbers, eliminating the need for manual calculation.

Introduction to the MEDIAN Function

The MEDIAN function returns the middle value in a set of numbers. Unlike AVERAGE, which sums all values and divides by count, MEDIAN identifies the central point where half the values are higher and half are lower. This distinction matters significantly when dealing with outliers.

Consider a team of five employees earning $45K, $48K, $50K, $52K, and $250K. The average salary is $89K—a misleading figure since four out of five earn under $53K. The median salary of $50K accurately represents the typical employee’s compensation.

Use MEDIAN when:

• Your data contains outliers that skew the average
• You need to understand typical values rather than mathematical means
• Analyzing salaries, home prices, or response times
• Working with skewed distributions

The basic syntax is straightforward:

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

Here’s a simple example with seven values:

=MEDIAN(23, 45, 67, 89, 102, 156, 234)

This returns 89, the middle value when sorted. Excel handles the sorting internally—you don’t need to arrange values beforehand.

Basic MEDIAN Function Usage

The most common application uses cell ranges rather than hardcoded values. This approach updates automatically when source data changes.

For a continuous range:

=MEDIAN(A1:A10)

This calculates the median of all numeric values in cells A1 through A10. Excel automatically ignores empty cells and text entries, so you don’t need to clean your data first.

For non-contiguous ranges, separate them with commas:

=MEDIAN(A1:A5, C1:C5, E1:E5)

This combines three separate ranges into a single median calculation. Excel treats all values as one dataset, not as three separate groups.

Understanding odd versus even counts matters conceptually but not practically—Excel handles both automatically. With an odd count (5, 7, 9 values), MEDIAN returns the exact middle value. With an even count (6, 8, 10 values), it averages the two middle values.

Example with five values:

=MEDIAN(10, 20, 30, 40, 50)

Returns 30 (the third value in sorted order).

Example with six values:

=MEDIAN(10, 20, 30, 40, 50, 60)

Returns 35 (the average of 30 and 40, the two middle values).

You can also reference entire columns, though this impacts performance with large datasets:

=MEDIAN(A:A)

This calculates the median of all numeric values in column A, excluding the header if it contains text.

MEDIAN with Criteria (Array Formulas)

Real-world analysis often requires conditional medians—finding the middle value only for data meeting specific criteria. This is where MEDIAN becomes powerful when combined with other functions.

The traditional approach uses an array formula with IF:

=MEDIAN(IF(A1:A10>100, B1:B10))

This calculates the median of values in B1:B10 where the corresponding A column value exceeds 100. In older Excel versions, you must enter this as an array formula using Ctrl+Shift+Enter. Excel 365 handles this automatically.

For category-based filtering:

=MEDIAN(IF(A1:A10="North", B1:B10))

This returns the median of B column values where the A column contains “North”.

Excel 365 users have a cleaner option with the FILTER function:

=MEDIAN(FILTER(B1:B10, A1:A10="North"))

This is more readable and performs better with large datasets. FILTER returns an array of values meeting your criteria, which MEDIAN then processes.

You can combine multiple criteria:

=MEDIAN(FILTER(C1:C100, (A1:A100="North") * (B1:B100>1000)))

This calculates the median of C column values where A equals “North” AND B exceeds 1000. The multiplication operator acts as an AND condition.

For OR conditions, use addition:

=MEDIAN(FILTER(C1:C100, (A1:A100="North") + (A1:A100="South")))

Practical Applications and Use Cases

Let’s examine a complete sales analysis scenario. You have regional sales data and want to understand typical performance while ignoring extreme outliers.

Dataset structure:

• Column A: Region (North, South, East, West)
• Column B: Sales Rep Name
• Column C: Monthly Sales Amount

Calculate median sales by region:

=MEDIAN(FILTER(C2:C101, A2:A101="North"))

Create a summary dashboard:

This reveals how much outliers affect regional averages. A large difference suggests inconsistent performance or data quality issues.

Salary analysis example:

=MEDIAN(IF(Department="Engineering", Salary))

This provides the typical engineering salary, unaffected by executive compensation or entry-level positions.

Real estate price analysis:

=MEDIAN(FILTER(Price, (Bedrooms=3) * (Neighborhood="Downtown")))

This returns the median price for three-bedroom downtown properties, giving buyers realistic expectations despite luxury penthouse outliers.

Response time monitoring:

=MEDIAN(IF(HOUR(Timestamp)>=9, IF(HOUR(Timestamp)<=17, ResponseTime)))

This calculates median response time during business hours only, excluding overnight periods with different staffing.

Common Errors and Troubleshooting

The #NUM! error occurs when MEDIAN receives no numeric values:

=MEDIAN(A1:A10)  // Returns #NUM! if all cells are empty or contain text

Prevent this with IFERROR:

=IFERROR(MEDIAN(A1:A10), "No data available")

The #VALUE! error appears with array formulas in older Excel versions when you forget Ctrl+Shift+Enter. Excel 365 eliminates this issue with automatic array handling.

Empty cells don’t cause errors—Excel ignores them automatically. However, cells containing zero are included in calculations. If zeros represent missing data rather than actual zero values, filter them out:

=MEDIAN(IF(A1:A10<>0, A1:A10))

For more robust error handling with conditional medians:

=IFERROR(MEDIAN(FILTER(B1:B100, A1:A100="Category")), "No matching records")

This returns a helpful message when your filter criteria match no records.

Text values mixed with numbers are ignored, but be aware this might produce unexpected results if you’re unaware of data quality issues. Always validate your source data.

MEDIAN vs Alternatives

Understanding when to use MEDIAN versus other statistical functions improves analysis quality.

MEDIAN vs AVERAGE comparison:

// Dataset: 10, 15, 20, 25, 1000
=AVERAGE(A1:A5)  // Returns 214
=MEDIAN(A1:A5)   // Returns 20

MEDIAN better represents this dataset because one outlier (1000) severely skews the average.

When to use QUARTILE:

=QUARTILE.INC(A1:A100, 1)  // 25th percentile
=MEDIAN(A1:A100)            // 50th percentile (same as QUARTILE 2)
=QUARTILE.INC(A1:A100, 3)  // 75th percentile

Use QUARTILE when you need to understand data distribution beyond the center point. The interquartile range (Q3 - Q1) measures spread while ignoring outliers.

When to use PERCENTILE:

=PERCENTILE.INC(A1:A100, 0.90)  // 90th percentile value

PERCENTILE offers more granularity than QUARTILE. Use it for performance thresholds, SLA monitoring, or identifying top/bottom performers.

Combining statistics for comprehensive analysis:

// Cell D1: =AVERAGE(A1:A100)
// Cell D2: =MEDIAN(A1:A100)
// Cell D3: =MODE.SNGL(A1:A100)
// Cell D4: =STDEV.P(A1:A100)

MODE identifies the most frequently occurring value—useful for categorical data or identifying common price points. When AVERAGE and MEDIAN differ significantly, investigate why. When MODE differs from both, you likely have a bimodal distribution requiring segmented analysis.

The MEDIAN function excels at providing reliable central tendency measures for real-world data. Master it alongside conditional formulas like FILTER and IF, and you’ll build robust analytical models that handle messy data gracefully. Whether analyzing compensation, monitoring system performance, or evaluating market prices, MEDIAN cuts through outliers to reveal typical values that inform better decisions.