MEDIAN returns the middle numeric value after ordering the data internally. For an even count, it averages the two middle numbers. The source cells stay in their original order.
MEDIAN function syntax
=MEDIAN(value1, [value2, ...])- value1: the first number or range to include.
- Additional arguments can supply more numbers or ranges.
- Text and blank cells in a range are ignored. Numeric zero is included.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Region | Score |
|---|---|
| East | 80 |
| West | 60 |
| East | 90 |
| West | 70 |
| East | 100 |
Find the middle score
Enter this formula in A9. Sorting the five scores gives 60, 70, 80, 90 and 100. The middle score is 80, regardless of the input order.
=MEDIAN(B2:B6)Result: 80.
Average the two middle values
These six order amounts have middle values of 60 and 80. Their average is 70. The median does not need to be a value that appears in the original list.
=MEDIAN(30,45,60,80,100,120)Result: 70.
Ignore blank and text cells
Enter the sales table at A20. The blank Tuesday cell and the text Closed are ignored. The median of the three numeric sales amounts is 30.
| Day | Sales |
|---|---|
| Mon | 10 |
| Tue | |
| Wed | 30 |
| Thu | Closed |
| Fri | 50 |
=MEDIAN(B21:B25)Result: 30.
Calculate a median for one region
FILTER keeps the East scores: 80, 90 and 100. MEDIAN returns 90 for that subset. Keep the value and condition ranges aligned row for row.
=MEDIAN(FILTER(B2:B6,A2:A6="East"))Result: 90.
Compare an outlier with the average
These five illustrative prices have a median of 300000, while their average is 1234000. Making only the largest value still larger would change the average but not this median.
=MEDIAN(250000,280000,300000,340000,5000000)Result: 300000.
Exclude zero while keeping negative values
This filtered list removes only zero. The remaining values are -10, 20 and 30, whose median is 20. A >0 filter would also discard negative values.
=MEDIAN(FILTER({-10;0;20;30},{-10;0;20;30}<>0))Result: 20.
Other Google Sheets articles you may also like