How to Use the MEDIAN Function in Google Sheets
=MEDIAN(value1, [value2, ...]). Unlike AVERAGE, one very large or very small value does not drag it around.
What the median is
Put the numbers in order and pick the one in the middle. That is the median. Per Google's help page, with an odd number of values the median is the middle value, and with an even number it is the average of the two middle values. It is the center of the data, not its mean.
MEDIAN syntax
=MEDIAN(value1, [value2, ...])
Each argument can be a number or a range. Text in the arguments is ignored. Google's page mentions a limit of 30 arguments but notes that Sheets accepts any number.
A step-by-step example
Here are five order values in cells A1:A6. One of them, 100, is much larger than the rest.
| A | |
|---|---|
| 1 | Order |
| 2 | 12 |
| 3 | 7 |
| 4 | 30 |
| 5 | 9 |
| 6 | 100 |
=MEDIAN(A2:A6)
This returns 12. Here is why: in order the values are 7, 9, 12, 30, 100, and the middle one is 12. Compare it with the average:
=AVERAGE(A2:A6)
This returns 31.6. The single large order pulls the average far above what a typical order looks like. The median stays at 12.
An even number of values
With four values, no single number is in the middle, so MEDIAN averages the two middle ones. =MEDIAN(1, 2, 3, 4) returns 2.5.
Common mistakes
- Expecting the mean. For the mean, use AVERAGE.
- Numbers stored as text. Text is ignored, so those values silently drop out of the result.
- Including a total row. A SUM in the range is treated as one more data point.
Try it yourself
Use the values above (A2:A6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Middle order
Write a formula that returns the median of the values in A2:A6.
Exercise 2: Median of four
Write a formula that returns the median of the four numbers 1, 2, 3 and 4 typed directly into the formula.
What to learn next
- AVERAGE: find the mean of a range.
- MIN and MAX: find the smallest and largest values.
- LARGE and SMALL: find the nth largest or smallest value.
Syntax and rules in this guide were checked against Google's MEDIAN help page. See all guides.