How to use the MEDIAN function
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.
Table of contents
1. MEDIAN Function Syntax
MEDIAN(number1, [number2], ...)
2. MEDIAN Function Arguments
number1 | Required. A constant or a reference to a cell or cell range which contains numbers. |
[number2] | Optional. Up to 254 additional arguments. |
3. MEDIAN Function Example
The picture above demonstrates the MEDIAN function in cell D3. The data is in cell range B2:B8, here it is:
Numbers |
1 |
2 |
2 |
6 |
1 |
2 |
The following formula in cell D3 calculates the median value based on the values in cell range B3:B8:
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 = 2 This value matches the number calculated in cell D3.
To illustrate using another data set, the middle number of 1, 1, 2, 3, 4, 6 is not possible, we need to calculate the average of the two middle numbers: 2.5. (2+3)/2 = 2.5
This data set has a middle number which is the median: 1, 1, 2, 3, 4, 6, and 7. The middle number is 3 in this specific data set.
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.
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. Here are the values in cell range B4:N8:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
North | 28 | 14 | 722 | 577 | 427 | 722 | 309 | 875 | 449 | 860 | 976 | 485 |
West | 429 | 976 | 156 | 576 | 688 | 509 | 137 | 433 | 961 | 137 | 293 | 520 |
South | 313 | 811 | 184 | 601 | 356 | 184 | 927 | 829 | 512 | 445 | 467 | 871 |
East | 443 | 888 | 27 | 288 | 981 | 100 | 166 | 673 | 590 | 90 | 169 | 926 |
Excel 365 LAMBDA function in cell B13:
For example, the first row (North) contains these numbers: 28, 14, 722, 577, 427, 722, 309, 875, 449, 860, 976, and 485
The number of values are 12 which is an even number, median is therefore the average between 577 and 485. 485 + 577 = 1062 1062/2 = 531
531 is the calculated median for column "North" in the image above in cell P5 which matches our manually calculated value.
The formula in cell B13 returns the rows in sorted order from small to large based on their median numbers. Row "North" is at the top since the median value is the smallest in the group of rows.
This type of calculations using formulas were much harder in earlier Excel versions than Excel 365, the formula I created above is small and easy to understand.
Explaining formula
The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.
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
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. Here is the data table displayed in cell range B4:N8 in the image above:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
North | 28 | 14 | 722 | 577 | 427 | 722 | 309 | 875 | 449 | 860 | 976 | 485 |
West | 429 | 976 | 156 | 576 | 688 | 509 | 137 | 433 | 961 | 137 | 293 | 520 |
South | 313 | 811 | 184 | 601 | 356 | 184 | 927 | 829 | 512 | 445 | 467 | 871 |
East | 443 | 888 | 27 | 288 | 981 | 100 | 166 | 673 | 590 | 90 | 169 | 926 |
The median values calculated column-wise from cell range C5:N8 are: {371, 849.5, 170, 576.5, 557.5, 346.5, 237.5, 751, 551, 291, 380, 695.5} These values are shown in row 10 in the image above.
Excel 365 LAMBDA function in cell C14:
The formula in cell C14 returns the columns in sorted order from small to large based on their median numbers. Column "Mar" is the left-most column since the median value is the smallest in the group of rows, in this example the median value is 170.
Explaining formula
The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.
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
'MEDIAN' function examples
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
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 73 functions in the 'Statistical' category.
Excel function categories
Excel categories
One Response to “How to use the MEDIAN function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Great article, Oscar! I really appreciated how you explained the difference between using the median and the mean, and how outliers can affect the latter. It was interesting to see in your examples how the mean can be distorted by a few very high incomes or an outlier, whereas the median gives a better representation of the typical value in a dataset. Keep up the good work!