Description
Use this function to calculate the median (middle value) of a given set of numbers. Supported in Chains. Can be used with CHILDREFS.
MEDIAN returns the middle value in a set of numbers. If there's an even number of values, it calculates the average of the two numbers at the middle of the range.
Syntax
MEDIAN(number_1, […,number_254])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
number_1 |
Yes | The first numeric value that you want to calculate the median for. | A number, a reference to a cell containing a number, a cell range, or a formula which results in any of these. |
number_n |
No | Additional numbers or ranges to be included in the calculation. | A number, a reference to a cell containing a number, a cell range, or a formula which results in any of these. Up to 254 additional values can be provided. |
Example
Sample data
| A | B | |
|---|---|---|
1 |
Value | Description |
2 |
10 | Score 1 |
3 |
15 | Score 2 |
4 |
20 | Score 3 |
5 |
25 | Score 4 |
6 |
30 | Score 5 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the middle value of an entire data set. | =MEDIAN(A2:A6) |
Sorts all five scores and returns the one in the middle. Result: 20 |
| Find the middle value among numbers typed directly into the formula. | =MEDIAN(10, 20, 30) |
Sorts three literal values and returns the one in the middle. Result: 20 |
| Find the middle value across two separate, non-adjacent ranges. | =MEDIAN(A2:A3, A5:A6) |
Combines scores 1, 2, 4, and 5 (skipping score 3), then averages the two middle values since there are four total. Result: 20 |
| Find the middle value of a data set with an even number of values. | =MEDIAN(A2:A5) |
Averages the two middle values of the first four scores, since there's no single middle value with an even count. Result: 17.5 |
| Find the middle value of a range plus extra values typed directly into the formula. | =MEDIAN(5, A2:A6, 35) |
Adds 5 and 35 to the five scores, then returns the middle value of all seven combined. Result: 20 |
Notes
- MEDIAN ignores empty cells, logical values, and text in the supplied values.
- If the range contains no numbers, MEDIAN returns the #NUM! error.
- Arguments can be numbers, named ranges, arrays, or references to cells containing numbers.
- MEDIAN is particularly useful for skewed distributions where the average might not be representative.
- MEDIAN is a measure of central tendency, along with AVERAGE. However:
- MEDIAN is less affected by outliers and skewed data than AVERAGE.
- MEDIAN represents the middle value when the numbers are ordered from least to greatest. This means that:
- For datasets with an odd number of values, MEDIAN returns the middle number.
- For datasets with an even number of values, MEDIAN returns the average of the two middle numbers.
Tips
- Use MEDIAN when you need a measure of central tendency that's less affected by extreme values than AVERAGE.
- Consider using MEDIAN in conjunction with AVERAGE to get a more comprehensive view of your data's central tendency.
- MEDIAN can be particularly useful in analyzing salary data, housing prices, or other datasets with potential outliers.
- Combine MEDIAN with other statistical functions like QUARTILE or PERCENTILE or more in-depth data analysis.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA