Exceljet

Quick, clean, and to the point

Excel MEDIAN Function

Excel MEDIAN function
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], ...)
Arguments 
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.
Version 
Usage notes 

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, upp to 255 total.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas