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.