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.

1. Syntax

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

2. 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. 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.

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. 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

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. 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

Back to top

7. Units contained in a range that overlap another range

This section demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles dates as numbers.

This makes it possible to use the methods shown in this section with Excel date and time values. An Excel date is really only an integer (whole number) formatted as a date. For example, 1/1/1900 is 1. 1/1/2000 is 36526.

There are 36525 days between 1/1/1900 and 1/1/2000, you can verify this yourself. Type 1/1/2000 in a cell, select the cell again. Press CTRL + 1 to open the "Format cells" dialog box. Press with left mouse button on "General"

Now we know how Excel handles dates. This makes it possible to use the MEDIAN function to calculate the number of overlapping days between two date ranges.

I have five examples below that demonstrate the formula calculations in great detail, one is added to the end number of both ranges and the reason is that date ranges count the end date contrary to numerical ranges.

For example, numerical range 5 to 10 is 10 - 5 equals 5. However, date range 1/5/2020 to 1/10/2020 contains 6 days. That is the reason the formulas below are adding 1 to the end dates of both date ranges.

I got this from Carol Weideman, thank you! I think it is really simple and clever.

Date range 1: A to B and date range 2: C to D

Formula:

MEDIAN(number1, [number2], ..)
Returns the median, or the number in the middle of the set of given numbers

The MEDIAN function returns the middle number based on a set of given numbers, the formula above uses two MEDIAN functions and each function contains three numbers.

The first MEDIAN function contains the start date, end date + 1 of the first date range and the end date + 1 of the second date range. The second MEDIAN function contains the start date, end date + 1 of the first date range and the start date of the second date range.

Example 1 - No overlapping ranges

This example has two ranges that don't overlap, this demonstrates the calculations behind the formula when it returns 0 (zero) overlapping units.

The first range begins at 10 and ends at 15, the second range begins 5 and ends at 8. The second range is located before the first range, example 5 demonstrates when the second range is located after the first range.

becomes

=MEDIAN(10,15+1,8+1)-MEDIAN(10,15+1,5)

becomes

=MEDIAN(10,16,9)-MEDIAN(10,16,5)

becomes

10 - 10 = 0

0 (zero) units are overlapping.

Example 2 - Partially overlapping ranges

This example has two numerical ranges that partially overlap, see image above. The first range begins at 10 and ends at 15.

The second range begins at 7 and ends at 10, the ranges overlap by one unit. Example 4 shows the same setup except that the second range overlaps the last unit of the first range.

becomes

=MEDIAN(10,15+1,10+1)-MEDIAN(10,15+1,7)

becomes

=MEDIAN(10,16,11)-MEDIAN(10,16,7)

becomes

11 - 10 = 1

1 unit is overlapping.

Example 3 - Overlapping ranges

This example demonstrates when two ranges completely overlap each other, the first range starts at 10 and ends at 15.

The second range starts at 11 and ends at 14, four units overlap in total which the image above shows.

becomes

=MEDIAN(10,15+1,14+1)-MEDIAN(10,15+1,11)

becomes

=MEDIAN(10,16,15)-MEDIAN(10,16,11)

becomes

15 - 11 = 4

4 units are overlapping.

Example 4 - Partially overlapping ranges

This example shows two ranges that partially overlap, the first range starts at 10 and ends at 15. The second range starts at 15 and ends at 18.

Both ranges overlap at 15 and only one unit is overlapping, see image above.

becomes

=MEDIAN(10,15+1,18+1)-MEDIAN(10,15+1,15)

becomes

=MEDIAN(10,16,19)-MEDIAN(10,16,15)

becomes

16 - 15 = 1

1 unit is overlapping

Example 5 - No overlapping ranges

The last example

becomes

=MEDIAN(10,15+1,20+1)-MEDIAN(10,15+1,17)

becomes

=MEDIAN(10,16,21)-MEDIAN(10,16,17)

becomes

16 - 16 = 0

0 units are overlapping.

Can these calculations be done with multiple date ranges?

Yes, they can. However, the MEDIAN function is not suitable for array calculations so the formulas are somewhat more complicated.

I have written an article that demonstrates a formula that tells you if a date range is overlapping any other date range specified in a list. It returns True if it is overlapping and False if not.

What if you want to know exactly which date range overlaps?
Check out this article: Identify rows of overlapping records The formula returns the row number or rows of all date ranges that overlap.

Can I use the MEDIAN function to calculate time ranges?
Yes, you can. I have written this article that shows the formula calculations in detail:
How to calculate overlapping time ranges

8. Function not working

The MEDIAN function returns

• #NAME? error if you misspell the function name.
• propagates errors, meaning that if the input contains an error (e.g., #VALUE!, #REF!), the function will return the same error.

8.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

8.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue.

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B3:B8 converted to hard-coded value using the F9 key. The MEDIAN function requires non-error values which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

8.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

'MEDIAN' function examples

Functions in 'Statistical' category

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

Excel function categories

Excel categories

Comments (1)