Summary

The Excel MEDIAN function returns the median (middle number) in the supplied set of data. For example, =MEDIAN(1,2,3,4,5) returns 3.

Purpose 

Get the median of a group of numbers

Return value 

A number representing the median.

Syntax

=MEDIAN(number1,[number2],...)
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.

How to use 

The MEDIAN function returns the median (middle number) in a set of data. The calculation performed by MEDIAN varies according to the number of numeric values provided. When the number is odd, MEDIAN returns the middle number in the group. When the number is even, MEDIAN returns the average of the two numbers in the middle. 

The MEDIAN function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MEDIAN ignores empty cells, text values, and the logical values TRUE and FALSE. The MEDIAN function will accept up to 255 separate arguments. 

Examples

When the number of values provided is odd, MEDIAN returns the middle number:

=MEDIAN(1,2,3,4,5) // returns 3
=MEDIAN(1,4,5,7,11) // returns 5

When the number of values provided is even, MEDIAN returns the average of the two middle numbers:

=MEDIAN(1,2,3,4,5,6) // returns 3.5
=MEDIAN(1,2,4,6,8,9) // returns 9

In the worksheet shown above, the formulas in H5 and H6 are:

=MEDIAN(B5:B16) // returns 83.5
=MEDIAN(D5:D16) // returns 80

Note that MEDIAN ignores the empty cell in D5:D16 and returns the middle number in the eleven values provided.

Notes

  • When the count of numbers is odd, MEDIAN returns the middle number.
  • When the count of numbers is even, MEDIAN returns the average of the two middle numbers.
  • MEDIAN ignores empty cells, the logical values TRUE and FALSE, and text.
  • MEDIAN returns a #NUM! error if no numeric values are provided.
  • Arguments can be numbers, names, arrays, or references, up to 255 total.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.