When analysing numerical datasets such as employee salaries, real estate prices, web traffic metrics, or survey responses in Google Sheets, calculating the central tendency is often the first step. While many users instinctively rely on the AVERAGE function, averages can be severely skewed by extreme outliers (such as a single massive sale or an executive salary that distorts the typical figure).
The median represents the exact numerical midpoint of a dataset: half the values fall above the median, and half fall below it. Google Sheets provides a dedicated MEDIAN function that calculates this figure automatically without requiring you to manually sort your data first.
Understanding the MEDIAN Formula Syntax
The syntax for the MEDIAN function is clean and flexible:
=MEDIAN(value1, [value2, ...])value1: The first cell, range, or array of numbers to evaluate.value2, ...: [Optional] Additional numbers or ranges to include in the calculation.
How the Median Calculation Works
- Odd Number of Values: If your range contains an odd count of numbers (e.g.,
10, 20, 30, 40, 50), the median is the single middle value (30). - Even Number of Values: If your range contains an even count of numbers (e.g.,
10, 20, 30, 40), the median is the arithmetic mean of the two middle numbers ((20 + 30) / 2 = 25). - Text and Empty Cells:
MEDIANautomatically ignores empty cells, text labels, and booleanTRUE/FALSEvalues within a range, preventing calculation errors.
Basic Example: Finding the Median of a Data Range
To calculate the median across a column of numbers:
- Open your spreadsheet in Google Sheets.
- Click in the cell where you want your result to appear.
- Enter the formula referencing your data range:
=MEDIAN(B2:B50)- Press Enter. Google Sheets instantly outputs the midpoint value.
Median vs Average: When to Use Which?
To understand why the median is often more reliable than the average, compare how both functions interpret skewed data:
| Sample Salaries | Average (Mean) | Median |
|---|---|---|
| £30,000, £32,000, £35,000, £38,000, £250,000 | £77,000 | £35,000 |
In this example, the £250,000 executive salary inflates the average to £77,000—a figure that misrepresents 80% of the team. The median of £35,000 provides an accurate reflection of what a typical team member earns.
Advanced: Calculate Median with Criteria Using MEDIAN and FILTER
Unlike AVERAGEIFS or COUNTIFS, Google Sheets does not have a native MEDIANIFS function. However, you can easily calculate a conditional median by combining MEDIAN with the FILTER function:
Suppose column A contains department names (e.g., “Marketing”) and column B contains salaries. To calculate the median salary for only the Marketing department:
=MEDIAN(FILTER(B2:B100, A2:A100 = "Marketing"))The FILTER function extracts only the salary numbers where the department matches “Marketing”, and passes that filtered array directly into MEDIAN.
Incorporating the MEDIAN function into your Google Sheets reports protects your data analysis from distortion and delivers genuine statistical insights into your datasets.