MEDIAN Function in Excel, Google Sheets, Airtable & Notion
MEDIAN sorts the numbers and returns the one in the middle. With an even count it returns the average of the two middle values.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | MEDIAN(number1, [number2], ...) |
| Google Sheets | ✔ Built in | MEDIAN(value1, [value2, ...]) |
| Airtable | ✘ Not available | — Airtable has no MEDIAN function, and rollup formulas cannot sort values. Use the median in a view's summary bar, or calculate it in Excel or Sheets. |
| Notion | ✔ Built in | median(number | list, ...)Accepts numbers or a list, e.g. median(prop("Scores")) or a rollup. Lists must contain numbers only. |
| Excel | =MEDIAN(A2:A7) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =MEDIAN(A2:A7) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable has no MEDIAN function, and rollup formulas cannot sort values. Use the median in a view's summary bar, or calculate it in Excel or Sheets. | |
| Notion | median([3, 8, 1, 12, 7, 5]) | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Value 3 8 1 12 7 5
Arguments
| Argument | What it means |
|---|---|
number1 | The first number, cell or range. |
[number2], ... optional | More numbers or ranges. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=MEDIAN(3, 8, 1, 12, 7, 5) | 6 | Sorted: 1, 3, 5, 7, 8, 12. The two middle values 5 and 7 are averaged. | |
=MEDIAN(3, 8, 1, 12, 7) | 7 | Odd count: the single middle value of 1, 3, 7, 8, 12. | |
=MEDIAN(40, 42, 45, 48, 500) | 45 | The outlier 500 barely matters; AVERAGE would be 135. |
Examples use Excel / Google Sheets syntax unless noted.
How MEDIAN works
The median is the value that splits the data in half: half the numbers are below it and half above. Because it only depends on the order of the values, a few extreme numbers do not move it much, which makes it the usual choice for incomes, house prices, delivery times and anything else with a long tail. Compare it with AVERAGE: if the two are far apart, your data is skewed.
The data does not need to be sorted. Like AVERAGE, MEDIAN skips empty cells, text and logical values inside a range, and counts zeros. With no numbers at all it returns #NUM!. For other split points (the 25th or 90th percentile) use PERCENTILE; the median is the same as PERCENTILE(range, 0.5).
Notion has a built-in median() that takes numbers or a list. Airtable has no median in formulas or rollups. The summary bar at the bottom of a grid view can show a median for a number field; otherwise export the data or sync it to a spreadsheet.
Frequently Asked Questions
- What does MEDIAN return for an even number of values?
- The average of the two middle values, so it may not be one of your numbers.
- Should I use MEDIAN or AVERAGE?
- AVERAGE uses every value and is pulled by outliers. MEDIAN is the middle value and ignores how extreme the outliers are. Use MEDIAN for skewed data.