What is the MEDIAN function?

The MEDIAN function calculates the median based on a set of numbers in Excel. The median value is the middle number of a group of numbers when arranged from smallest to largest.

It returns a good measure of the typical value in a data set that is not affected by outliers or skewness . For example, the median income may be a better measure of the average income than the mean. The mean can be distorted by a few very high incomes which is the case, for instance, in the US. The very high incomes represent the outliers.

1. MEDIAN Function Syntax

MEDIAN(number1, [number2], ...)

2. MEDIAN Function Arguments

3. MEDIAN Function example

The picture above demonstrates the MEDIAN function in cell D3.

Formula in cell D3:

If the group contains an even number of numbers the MEDIAN function calculates the average of the two numbers in the middle.

There is no middle number in this group: 1, 1, 2, 2, 2, 6. To calculate the median in this case you need to calculate the average of the two middle numbers. It is calculated like this: (2+2)/2

Another example, the middle number of 1, 1, 2, 3, 4, 6 is 2.5.  (2+3)/2 = 2.5

Back to top

4. How the average and the median can differ

The image above shows the income of two different groups in cells B3:B9 and D3:D9 respectively, the data is made-up. The average incomes of these two groups are calculated in cells G3:G4, the calculations show that group 1 has a higher average income than group 2.

However, group 2 has a higher median income than group 1. How is this possible? Group 1 has an outlier that distorts the average (mean) but not the median.

Formula in cell H3:

The chart displays the two groups, group 1 is blue and group 2 is yellow. Both groups are sorted from small to large in order to easily spot the middle number which is the median. Group 1 has an outlier that is drastically larger than the others.

Back to top

5. Sort rows based on the median values

This image demonstrates a formula in cell B13 that calculates the median for each row then sorts the data in cells B5:N8 by row from small to large. All this is done in one single formula.

To show that the calculation is correct column P contains the median numbers, the formula in cell B13 is an Excel 365 dynamic array formula meaning it spills values to cells below and to the right as far as needed.

Excel 365 LAMBDA function in cell B13:

Explaining formula

Step 1 - Calculate the median for variable a

The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a group of numbers.

Function syntax: MEDIAN(number1, [number2], ...)

MEDIAN(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,MEDIAN(a))

Step 3 - Calculate the median by row

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(C5:N8,LAMBDA(a,MEDIAN(a)))

returns {531; 471; 489.5; 365.5}

Step 4 - Sort rows based on the median values

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(B5:N8,BYROW(C5:N8,LAMBDA(a,MEDIAN(a))),1)

becomes

SORTBY(B5:N8,{531; 471; 489.5; 365.5}, 1)

and returns

Back to top

6. Sort columns based on the median values

The following formula sorts columns based on the median values calculated column-wise from small to large. Row 20 shows the medians values, they are sorted from small to large.

Excel 365 LAMBDA function in cell B13:

Explaining formula

Step 1 - Calculate the median for variable a

The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a group of numbers.

Function syntax: MEDIAN(number1, [number2], ...)

MEDIAN(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,MEDIAN(a))

Step 3 - Calculate the median by column

The BYCOL function passes all values in a column based on an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify.

Function syntax: BYCOL(array, lambda(array, calculation))

BYCOL(C5:N8,LAMBDA(a,MEDIAN(a)))

returns

{371, 849.5, 170, 576.5, 557.5, 346.5, 237.5, 751, 551, 291, 380, 695.5}

Step 4 - Sort columns based on the median values

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(B5:N8,BYROW(C5:N8,LAMBDA(a,MEDIAN(a))),1)

becomes

SORTBY(C4:N8,, 1)

and returns

Back to top

Get the Excel file

How-to-use-the-MEDIAN-functionv2.xlsx

'MEDIAN' function examples

How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Units contained in a range that overlap another range
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]

Functions in 'Statistical' category

The MEDIAN function function is one of many functions in the 'Statistical' category.

Excel categories

Comments (1)