How to Calculate the Median in Google Sheets

Key Insights

• The MEDIAN function in Google Sheets returns the middle value of a dataset, making it more reliable than AVERAGE for skewed data with outliers like salary figures or response times.
• Google Sheets lacks a native MEDIAN.IF function, but you can achieve conditional medians using the FILTER function: =MEDIAN(FILTER(values, criteria)).
• MEDIAN automatically ignores text and empty cells, but wrapping your formula in IFERROR prevents ugly error messages when working with dynamic or potentially empty ranges.

Introduction to Median in Google Sheets

The median is the middle value in a sorted dataset. If you have five numbers, the median is the third one when arranged in order. For even-numbered datasets, it’s the average of the two middle values. This simple concept becomes powerful when you’re analyzing data that doesn’t follow a normal distribution.

Consider employee salaries at a company. If nine people earn $50,000 and one executive earns $500,000, the average salary is $95,000—a number that represents nobody. The median? $50,000. That’s the reality for most employees.

Google Sheets provides robust built-in support for median calculations. The MEDIAN function handles the heavy lifting, but knowing its nuances—and workarounds for missing features—separates basic users from power users. Let’s dig into the practical details.

Using the Basic MEDIAN Function

The MEDIAN function follows Google Sheets’ standard syntax pattern. You pass it a range, and it returns the middle value.

=MEDIAN(A1:A10)

That’s it for basic usage. The function sorts the values internally and finds the center point. You never see the sorting happen—it just works.

Here’s a concrete example. Suppose column A contains daily website response times in milliseconds:

A1: 120
A2: 145
A3: 132
A4: 890
A5: 128
A6: 141
A7: 135
A8: 129
A9: 138
A10: 133

The formula =MEDIAN(A1:A10) returns 136.5. Notice that 890ms outlier? It barely affects the median but would significantly skew an average. The average of this dataset is 209.1ms—nearly 54% higher than the median and not representative of typical performance.

For odd-numbered datasets, the median is an actual value from your data. For even-numbered datasets like this one, Google Sheets averages the two middle values (135 and 138, giving us 136.5).

You can also pass individual cell references:

=MEDIAN(A1, A2, A3, A4, A5)

This approach works but becomes unwieldy for larger datasets. Stick with ranges when possible.

MEDIAN with Multiple Ranges and Values

Real-world data rarely sits in a single contiguous column. You might need to combine data from multiple sources or include hardcoded values for comparison purposes.

Google Sheets handles this elegantly. The MEDIAN function accepts multiple arguments, mixing ranges, individual cells, and literal values:

=MEDIAN(A1:A5, C1:C5, 100, 200)

This formula calculates the median across all values in A1:A5, all values in C1:C5, plus the numbers 100 and 200. The function treats everything as one combined dataset.

Practical scenario: You’re tracking customer support ticket resolution times across three regional teams stored in columns B, D, and F:

=MEDIAN(B2:B50, D2:D50, F2:F50)

This gives you the overall median resolution time without restructuring your spreadsheet.

You can also reference entire columns:

=MEDIAN(A:A)

Be cautious with this approach. It works fine for smaller datasets, but scanning entire columns impacts performance. More on that later.

For combining data with baseline comparisons:

=MEDIAN(A2:A100, 0)

Adding zero (or any reference value) to your median calculation lets you see how your data’s center relates to a specific benchmark. This technique proves useful when you want to include a target value in your analysis.

Conditional Median with MEDIAN.IF Workarounds

Here’s where Google Sheets disappoints: there’s no native MEDIAN.IF function. Excel added MEDIAN.IF in recent versions, but Google Sheets users need workarounds. Fortunately, good workarounds exist.

The cleanest solution uses the FILTER function:

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

This formula calculates the median of values in column B, but only where the corresponding cell in column A equals “Sales.” The FILTER function creates a virtual array of matching values, and MEDIAN operates on that array.

Let’s expand this with a real example. You have employee data:

To find the median salary for Sales:

=MEDIAN(FILTER(B2:B7, A2:A7="Sales"))

Result: 68000 (the middle value of 65000, 68000, and 72000).

You can add multiple conditions using multiplication (AND logic) or addition (OR logic):

=MEDIAN(FILTER(C2:C100, A2:A100="Sales", B2:B100>2020))

This returns the median of column C where column A is “Sales” AND column B is greater than 2020.

For OR conditions:

=MEDIAN(FILTER(C2:C100, (A2:A100="Sales")+(A2:A100="Marketing")))

The plus sign creates OR logic in array formulas. This returns the median for either Sales or Marketing departments.

An older approach uses IF as an array formula:

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

In older versions of Google Sheets, you’d need to press Ctrl+Shift+Enter to enter this as an array formula. Modern Google Sheets usually handles this automatically, but the FILTER approach is more readable and performs better.

For complex conditions involving calculations:

=MEDIAN(FILTER(B2:B100, MONTH(A2:A100)=6, YEAR(A2:A100)=2024))

This calculates the median for June 2024 data only.

Handling Errors and Edge Cases

MEDIAN is forgiving with mixed data. It automatically ignores text values and empty cells—no errors, no warnings. This behavior is usually what you want, but it can mask data quality issues.

A1: 100
A2: "pending"
A3: 150
A4: (empty)
A5: 200

=MEDIAN(A1:A5) returns 150, silently ignoring the text and empty cell.

However, error values break the function. If any cell contains #DIV/0!, #VALUE!, or similar errors, MEDIAN returns that error. Wrap your formula to handle this:

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

This returns “No valid data” instead of an error. For numeric fallbacks:

=IFERROR(MEDIAN(A1:A10), 0)

The FILTER workaround for conditional medians introduces another edge case. If no values match your criteria, FILTER returns an error, which propagates to MEDIAN:

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

Always wrap conditional medians in IFERROR when criteria might return zero results.

For single-value ranges, MEDIAN returns that value—no error. For completely empty ranges, it returns #NUM!. Handle accordingly:

=IF(COUNTA(A1:A10)=0, "Empty range", MEDIAN(A1:A10))

Practical Applications and Tips

Salary analysis remains the canonical use case. When presenting compensation data to stakeholders, median tells a more honest story than mean. “Our median salary is $75,000” means half your employees earn more, half earn less. Simple, accurate, defensible.

Response time monitoring benefits similarly. Whether you’re tracking API latency, customer service response, or manufacturing cycle times, outliers are common. A single timeout or escalated ticket shouldn’t distort your performance metrics. Use median.

Survey data with Likert scales (1-5 ratings) often works better with median. If most respondents rate something 4 or 5, but a few rate it 1, the median captures the predominant sentiment while mean gets pulled down.

Performance tip: MEDIAN on large datasets (50,000+ rows) can slow your spreadsheet. If you’re calculating multiple medians across massive ranges, consider:

1. Using helper columns with FILTER results, then running MEDIAN on smaller ranges
2. Moving complex calculations to a separate “analysis” sheet that doesn’t recalculate constantly
3. Using QUERY function to pre-filter data before applying MEDIAN

Comparing MEDIAN with AVERAGE and MODE:

• AVERAGE: Best for normally distributed data without significant outliers. Faster to calculate.
• MEDIAN: Best for skewed distributions or when outliers exist. Robust against extreme values.
• MODE: Best for categorical-like numeric data where you want the most common value. Less useful for continuous data.

A quick diagnostic: if your AVERAGE and MEDIAN differ by more than 10-15%, your data is likely skewed. Investigate the outliers and decide which measure better represents your use case.

For financial reporting, many organizations now mandate median alongside mean for executive compensation disclosures precisely because median resists manipulation through outliers.

The MEDIAN function in Google Sheets handles most scenarios cleanly. Master the FILTER combination for conditional calculations, wrap everything in IFERROR for production spreadsheets, and you’ll have reliable, meaningful statistics that actually represent your data.