MEDIAN Function in Google Sheets: Complete Guide

Key Insights

• MEDIAN is more resistant to outliers than AVERAGE, making it the better choice for analyzing salaries, housing prices, response times, and any dataset with extreme values that could skew results.
• Google Sheets lacks a native MEDIAN.IF function, but you can achieve conditional medians using FILTER or array formulas with IF—the FILTER approach is cleaner and doesn’t require Ctrl+Shift+Enter.
• MEDIAN automatically ignores text values, empty cells, and boolean values, which simplifies data cleaning but can produce unexpected results if you’re not aware of this behavior.

Introduction to MEDIAN

The median is the middle value in a sorted dataset. If you line up all your numbers from smallest to largest, the median sits right in the center. For datasets with an even count, it’s the average of the two middle values.

Why does this matter? Because the mean (average) lies to you when your data is skewed.

Consider salary data at a company where most employees earn between $50,000 and $80,000, but the CEO makes $2 million. The average salary might show $150,000—a number that represents nobody. The median would show something around $65,000, which actually reflects what a typical employee earns.

This resistance to outliers makes MEDIAN essential for:

• Salary and compensation analysis where executive pay skews averages
• Real estate pricing where luxury properties distort market perception
• Response time metrics where occasional timeouts inflate means
• Customer satisfaction scores where extreme ratings mislead
• Any dataset where outliers don’t represent typical behavior

Basic Syntax and Usage

The MEDIAN function accepts individual values, cell references, or ranges:

=MEDIAN(value1, [value2, ...])

Parameters:

• value1 (required): The first value or range to consider
• value2, ... (optional): Additional values or ranges

The function returns a single numeric value representing the median, or an error if no valid numeric data exists.

Here’s the simplest usage with a range:

=MEDIAN(A1:A10)

This calculates the median of all numeric values in cells A1 through A10.

You can also pass individual values directly:

=MEDIAN(5, 10, 15, 20, 25)

This returns 15—the middle value of the five numbers.

Combining ranges and individual values works too:

=MEDIAN(A1:A10, B1:B5, 100, 200)

Google Sheets treats all inputs as a single dataset, sorts them internally, and finds the middle value.

Handling Different Data Scenarios

Understanding how MEDIAN behaves with various data types prevents confusion and debugging headaches.

Odd vs. Even Number of Values

With an odd count, MEDIAN returns the exact middle value:

Dataset: 3, 7, 9, 12, 15
Sorted:  3, 7, 9, 12, 15
         ^middle
=MEDIAN(3, 7, 9, 12, 15)  → Returns 9

With an even count, MEDIAN averages the two middle values:

Dataset: 3, 7, 12, 15
Sorted:  3, 7, 12, 15
            ^--^middle pair
=MEDIAN(3, 7, 12, 15)  → Returns 9.5 (average of 7 and 12)

Ignored Values

MEDIAN silently ignores certain data types. Consider this dataset in A1:A6:

=MEDIAN(A1:A6)  → Returns 20

Only cells A1, A3, and A5 contribute. The text “hello”, the empty cell, and the boolean TRUE are all ignored. The median of {10, 20, 30} is 20.

This behavior is usually helpful—dirty data doesn’t break your formulas. But it can surprise you if you expect text-formatted numbers like “25” to be included. They won’t be.

Zero and Negative Values

Unlike some statistical edge cases, MEDIAN handles zeros and negative numbers without issues:

=MEDIAN(-50, -10, 0, 5, 100)  → Returns 0

Zero is a valid numeric value and participates in the calculation normally.

MEDIAN with Conditional Logic (MEDIAN.IF Workaround)

Google Sheets provides AVERAGEIF, SUMIF, and COUNTIF—but no MEDIAN.IF. This is a common frustration, but two workarounds solve it elegantly.

Using FILTER (Recommended)

The FILTER function creates a subset of your data that MEDIAN then processes:

=MEDIAN(FILTER(A2:A100, B2:B100="Sales"))

This calculates the median of values in column A, but only where the corresponding cell in column B equals “Sales”.

Multiple conditions work with multiplication (AND logic) or addition (OR logic):

// AND: Sales department in West region
=MEDIAN(FILTER(A2:A100, (B2:B100="Sales") * (C2:C100="West")))

// OR: Either Sales or Marketing department
=MEDIAN(FILTER(A2:A100, (B2:B100="Sales") + (B2:B100="Marketing")))

Using Array Formula with IF

The older approach uses IF as an array formula:

=MEDIAN(IF(B2:B100="Sales", A2:A100))

In modern Google Sheets, this works without special entry. In older versions or for compatibility, you might need to press Ctrl+Shift+Enter to enter it as an array formula.

The FILTER approach is cleaner and more readable. Use it unless you have a specific reason for the IF method.

Handling Empty FILTER Results

When FILTER matches nothing, MEDIAN throws an error. Wrap it defensively:

=IFERROR(MEDIAN(FILTER(A2:A100, B2:B100="Nonexistent")), "No data")

Combining MEDIAN with Other Functions

Real-world analysis rarely uses MEDIAN in isolation. Here are practical combinations.

Cross-Sheet Analysis with IMPORTRANGE

Pull data from another spreadsheet and calculate its median:

=MEDIAN(IMPORTRANGE("spreadsheet_url", "Sheet1!A2:A100"))

Note: You must authorize IMPORTRANGE access first by clicking the cell and allowing access when prompted.

For conditional medians across sheets:

=MEDIAN(FILTER(
  IMPORTRANGE("spreadsheet_url", "Sheet1!A2:A100"),
  IMPORTRANGE("spreadsheet_url", "Sheet1!B2:B100")="Active"
))

Dynamic Ranges with ARRAYFORMULA

Calculate rolling or dynamic medians:

// Median of last 30 entries (assuming data in A2:A)
=MEDIAN(FILTER(A2:A, ROW(A2:A) > MAX(ROW(A2:A)) - 30))

Using with QUERY Results

QUERY returns arrays that MEDIAN can process:

=MEDIAN(QUERY(A1:C100, "SELECT A WHERE B = 'Complete'", 0))

The 0 parameter suppresses headers. Without it, MEDIAN would ignore the header text anyway, but being explicit is cleaner.

For more complex queries:

=MEDIAN(QUERY(A1:D100, "SELECT A WHERE B = 'Sales' AND C > 1000", 0))

Common Errors and Troubleshooting

#NUM! Error

This occurs when MEDIAN receives no valid numeric data:

=MEDIAN(A1:A10)  // Where A1:A10 contains only text or is empty
→ #NUM!

Fix it by verifying your range contains numbers, or wrap with IFERROR:

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

#VALUE! Error

This typically happens with malformed IMPORTRANGE or FILTER expressions:

=MEDIAN(FILTER(A1:A10, B1:B5="Yes"))  // Mismatched range sizes
→ #VALUE!

Ensure all ranges in FILTER have identical dimensions.

Unexpected Results

If your median seems wrong, check for:

1. Text-formatted numbers: Cells displaying “25” as text won’t count. Convert them with VALUE() or multiply by 1.
2. Hidden rows: MEDIAN includes values in hidden rows. Use FILTER with a visibility column if needed.
3. Extra whitespace: " Sales" doesn’t equal “Sales”. Use TRIM in your conditions.

Debugging formula:

// Count how many values MEDIAN is actually using
=COUNT(FILTER(A2:A100, B2:B100="Sales"))

If this returns fewer than expected, investigate your filter condition.

Practical Applications

Application 1: API Response Time Dashboard

Track service performance where outliers (timeouts) shouldn’t dominate:

// Cell B2: Median response time
=MEDIAN(ResponseTimes!A2:A)

// Cell B3: 90th percentile for SLA monitoring
=PERCENTILE(ResponseTimes!A2:A, 0.9)

// Cell B4: Median by endpoint
=MEDIAN(FILTER(ResponseTimes!A2:A, ResponseTimes!B2:B="/api/users"))

Application 2: Regional Sales Comparison

Compare performance across regions without letting outlier deals skew perception:

Application 3: Complete Statistics Dashboard

Build a comprehensive view combining MEDIAN with related functions:

// Assuming sales data in A2:A with regions in B2:B

// Core statistics
=MIN(A2:A)                           // Minimum
=MEDIAN(A2:A)                        // Median (50th percentile)
=AVERAGE(A2:A)                       // Mean
=MAX(A2:A)                           // Maximum

// Spread analysis
=MAX(A2:A) - MIN(A2:A)               // Range
=PERCENTILE(A2:A, 0.75) - PERCENTILE(A2:A, 0.25)  // IQR

// Conditional medians for each region
=MEDIAN(FILTER(A2:A, B2:B="North"))
=MEDIAN(FILTER(A2:A, B2:B="South"))
=MEDIAN(FILTER(A2:A, B2:B="East"))
=MEDIAN(FILTER(A2:A, B2:B="West"))

When the mean significantly exceeds the median, your data is right-skewed (a few high values pulling the average up). When the median exceeds the mean, it’s left-skewed. This comparison alone tells you whether to trust the average or rely on the median for decision-making.

Use MEDIAN when you need to understand typical values. Use AVERAGE when every data point should contribute proportionally to the result. For most business metrics involving money, time, or human behavior, MEDIAN is the safer default.