Excel MEDIAN Function

last modified October 5, 2026

The MEDIAN function is a statistical function in Excel that returns the middle value in a dataset. Because it depends on the position of values rather than on every value, it is not dragged around by a single extremely high or low number the way the mean is. This tutorial provides a comprehensive guide to using the MEDIAN function with detailed examples. You'll learn its syntax, how it treats text, blanks, and logical values, and how it differs from AVERAGE.

MEDIAN Function Basics

The MEDIAN function returns the number in the middle of a sorted set of numbers. When the set contains an odd count of numbers, that is the single middle value; when the count is even, MEDIAN returns the average of the two middle numbers. The syntax is simple and similar to other statistical functions.

Component	Description
Function Name	MEDIAN
Syntax	=MEDIAN(number1, [number2], ...)
Arguments	1 to 255 numbers, names, arrays, or references
Return Value	Middle value of the supplied set

This table breaks down the essential components of the MEDIAN function. It shows the function name, basic syntax format, argument limits, and return value characteristics.

Argument	Meaning
number1	Required. The first number, cell reference, or range to evaluate.
number2, ...	Optional. Additional numbers, cell references, or ranges, up to a total of 255 arguments.

You can mix literals, cell references, and ranges in the same call. Inside a range, MEDIAN ignores empty cells, text, and logical values, but it includes cells that hold the number zero. Values typed directly into the argument list are treated differently: the Microsoft documentation states that logical values and text representations of numbers typed directly are counted. The examples below explore each of these cases.

Basic MEDIAN Example

This example demonstrates the simplest use of the MEDIAN function with an odd number of values.

=MEDIAN(1, 2, 3, 4, 5)

This formula finds the median of five numbers. The result is 3, the middle value in the ordered set (1,2,3,4,5). Because the count is odd, no averaging is needed.

MEDIAN with an Even Count

When the dataset has an even number of values, MEDIAN averages the two middle numbers. This example uses four values stored in a range.

A	B
10
20
30
40
	=MEDIAN(A1:A4)

The table shows four values in column A. The MEDIAN formula in B5 averages the two middle numbers (20 and 30) because the count is even.

=MEDIAN(A1:A4)

The ordered set is (10,20,30,40), so the median is (20+30)/2 = 25. A direct list behaves the same way: =MEDIAN(1, 2, 3, 4, 5, 6) returns 3.5, the average of 3 and 4.

MEDIAN with Text and Logical Values in a Range

Inside a range, MEDIAN ignores text and logical values and considers only the numbers. This example shows that behavior.

A	B
5
Text
15
TRUE
	=MEDIAN(A1:A4)

The table demonstrates how MEDIAN handles mixed content. It ignores the text in A2 and the logical value in A4, considering only the numbers in A1 and A3.

=MEDIAN(A1:A4)

The formula calculates the median of A1 (5) and A3 (15), ignoring A2 and A4. With two numbers, the median is their average: (5+15)/2 = 10. This shows MEDIAN's filtering of non-numeric cells inside references.

Direct Logical and Text Arguments

Values typed straight into the argument list follow a different rule. The Microsoft documentation states that logical values and text representations of numbers typed directly are counted, so they do affect the result. This is a common source of confusion, because AVERAGE ignores the same values when they are typed directly.

Formula	Result
=MEDIAN(10, TRUE, FALSE)	1
=MEDIAN(TRUE, FALSE)	0.5
=MEDIAN("5", 10)	7.5
=MEDIAN("abc", 10)	#VALUE!

In the first formula TRUE is read as 1 and FALSE as 0, so the values are (10,1,0) and the middle one is 1. The second formula averages 0 and 1 to give 0.5. The third counts the text "5" as the number 5, producing the median of 5 and 10. Text that cannot be read as a number, such as "abc", raises a #VALUE! error.

=MEDIAN(10, TRUE, FALSE)   ' 1
=MEDIAN(TRUE, FALSE)       ' 0.5
=MEDIAN("5", 10)           ' 7.5

MEDIAN with Blank Cells

MEDIAN ignores blank cells, treating them as non-existent, but it does include cells that contain the number zero. This example shows blanks being skipped.

A	B
100
200
300
	=MEDIAN(A1:A4)

The table contains numbers and blank cells to show how MEDIAN handles empty cells. The blank cell A2 is completely ignored in the calculation.

=MEDIAN(A1:A4)

This formula calculates the median of A1 (100), A3 (200), and A4 (300). The blank A2 is ignored. The ordered set is (100,200,300), so the median is 200. If A2 held a zero instead of being empty, the set would be (0,100,200,300) and the median would be 150.

MEDIAN with Multiple Ranges

MEDIAN can work with multiple ranges or individual cells, combining all of the values before finding the median. This example shows this flexibility.

A	B	C
10	30
20	40
		=MEDIAN(A1:A2, B1:B2)

The table demonstrates MEDIAN working with two separate ranges (A1:A2 and B1:B2). The function combines all values before finding the median.

=MEDIAN(A1:A2, B1:B2)

This formula combines values from A1:A2 (10,20) and B1:B2 (30,40). The full dataset is (10,20,30,40), so the median is (20+30)/2 = 25. This shows MEDIAN's ability to work with multiple ranges.

MEDIAN vs AVERAGE

Unlike AVERAGE, MEDIAN is resistant to outliers. This example compares both functions on the same data, where one value is far larger than the rest.

A	B	C
100
110
120
1000
	=AVERAGE(A1:A4)	=MEDIAN(A1:A4)

The table shows how the extreme value (1000) affects AVERAGE versus MEDIAN. While AVERAGE is pulled upward, MEDIAN stays close to the typical values.

=MEDIAN(A1:A4)    ' 115
=AVERAGE(A1:A4)   ' 332.5

The ordered set is (100,110,120,1000), so the middle values are 110 and 120 and the median is 115. The average is (100+110+120+1000)/4 = 332.5, more than twice the median. This is why MEDIAN is preferred for skewed data such as incomes, house prices, or response times.

Conditional Median with IF

To take a median of only the values that meet a condition, combine MEDIAN with IF. The IF returns the matching values and FALSE for the rest, and MEDIAN ignores the FALSE values.

A	B
10	Yes
20	No
30	Yes
40	Yes
50	No
60	Yes
	=MEDIAN(IF(B1:B6="Yes", A1:A6))

The table lists values in column A and a category in column B. The formula takes the median of only the A values whose B cell equals "Yes".

=MEDIAN(IF(B1:B6="Yes", A1:A6))

The matching values are 10, 30, 40, and 60. The two middle values are 30 and 40, so the result is 35. Passing a range to IF makes this an array formula: in Microsoft 365 and Excel 2021 it is confirmed with a plain Enter, while in Excel 2019 and earlier it must be confirmed with Ctrl+Shift+Enter, which wraps the formula in curly braces.

MEDIAN with Named Ranges

MEDIAN works well with named ranges, improving formula readability. This example shows MEDIAN with a named range.

A	B
50
60
70
	=MEDIAN(Temperatures)

The table assumes cells A1:A3 are named "Temperatures". The MEDIAN formula references this named range instead of cell addresses, making the formula more understandable.

=MEDIAN(Temperatures)

This formula calculates the median of the "Temperatures" range (A1:A3). The result is 60, the middle value in the ordered set (50,60,70). Named ranges make formulas more maintainable.

MEDIAN with Error Values

If any cell in the MEDIAN range contains an error value, the entire MEDIAN formula returns that error. This example shows this behavior.

A	B
10
#DIV/0!
30
	=MEDIAN(A1:A3)

The table demonstrates how MEDIAN reacts when encountering error values in the range. The presence of #DIV/0! in A2 causes the entire MEDIAN to fail, similar to other statistical functions.

=MEDIAN(A1:A3)

This formula attempts to find the median of A1 (10), A2 (#DIV/0! error), and A3 (30). Instead of a numeric result, it returns #DIV/0!. Error handling is needed to calculate the median of the valid numbers.

MEDIAN Errors and Troubleshooting

Cause	Symptom	Fix
Range contains an error value	The same error is returned	Remove the error, or use AGGREGATE with the MEDIAN option to skip it
Every referenced cell is empty or text	#NUM! error	Make sure at least one numeric value is present
Non-numeric text typed directly as an argument	#VALUE! error	Supply a number, or reference a cell that contains it
Logical values or text inside a range	Skipped silently	Range values are ignored; type them directly if they must count
Numbers stored as text in cells	Fewer values than expected	Convert the text to numbers, for example with VALUE
Hidden or filtered rows change the result	Hidden values are included	MEDIAN includes hidden rows; use AGGREGATE to exclude them

The MEDIAN function is essential for statistical analysis in Excel. It provides a robust measure of central tendency that is not distorted by extreme values, which makes it the natural choice whenever a distribution is skewed. From simple lists to conditional calculations, MEDIAN identifies the true middle of a dataset efficiently. Remember that it sorts the values and averages the two middle values for an even count, that it ignores text, logical values, and blanks found in ranges while including zeros, and that logical values and numeric text typed directly as arguments are counted. Mastering these distinctions will make your medians reliable and easy to explain.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.