Updated August 23, 2023
Median Function in Excel (Table of Contents)
Median Function in Excel
A median function is categorized under the statistical function; This MEDIAN function returns the median of the numbers provided. This is the number in the middle of a set of numbers, separating the higher half of its value; it is the central aspect of the data set arranged in order of magnitude.
The values supplied as arguments do not need to be sorted in any particular for the function to work.
Median Formula in Excel
Below is the Median Formula in Excel :
Median Formula in Excel has the following arguments :
- Number 1 (required argument) – The number arguments are a set of single or more numeric values (or arrays of numeric values) for which you want to calculate the median.
- Number 2 (optional argument)
Steps of using the median function
- Select the Formulas tab & Click On More Function.
- Choose Statistical to open the Function drop-down list. Select MEDIAN in the list.
- Also, click on the Insert function icon, then manually write Median and search the formula.
- We get new function windows shown in the below mentioned picture.
- Then we have to enter the details. Put the Numbers value or Range value where you want to get the middle value of the series. Then Click On OK.
Shortcut of using the formula
Click on the cell where you want the result from the value, then put the formula as below mentioned.
How to Use Median Function in Excel?
The median function is quite simple to understand and use. In contrast, other Microsoft Excel functions have numerous arguments or parameters.
Example # 1 -Median Function on even Numbers
Let’s understand below mentioned table below and the data as a series of even numbers; now I want to know the middle no of the series; we can see that this is an even series of the set. So, on even numbers of the group, median functions pick the two middle numbers of value and get the average. So in this series, the two middle numbers of the set are 10 and 9; thus, if we get an average of 10 and 9 ( 10 + 9 ) / 2, then we get 9.5. Thus we can use the median function on the numbers of all sets to get the result.
Now we will apply the Median function on the above data:
= MEDIAN (A2: A17 )
The Result will be :
Example #2- Median on Odd Numbers of Group
As shown in example one, this is the same table; only remove one number for creating the odd numbers of the group.
So if we use the median function on an odd number of the set, we simply find the middle numbers of value in the series and get the result. For example, we can see that 9 is the exact middle value of the group.
Now we will apply the Median function in the below data:
= MEDIAN (E2: E16 )
The Result will be :
Explanation of the Median Function
There are two concepts of median function: odd numbers of the set and even numbers of the set.
If the data series is in odd numbers. The MEDIAN function in Excel finds the middle of a number set or series; this result value is the middle in a group of numbers when those numbers are listed in numerical order.
Suppose the data contains an even number of values. The median then is the average of the middle two numbers in the group – when sorted numerically.
Suppose we talk about the data type: numbers, dates, named ranges, dates, arrays, or references to cells containing digits. Number 1 is required; subsequent numbers are optional.
The median and average functions are approximately the same, but there is some difference between both formulas. Let’s understand mathematical logic.
In the below-given example, we will find the difference between the average and median function by the following set of series.
We can see that when we are using the median function for getting the middle number in a set of numbers listed in numeric order, as mentioned above, as per odd numbers, any mathematical calculation of series finally gets the result exactly in the middle value of set as 3.
When we use the average function, the numbers are added, or the sum of the total set, and then the total no of the count is divided. So the sum of the series is 18, and the total no of the count is 5, then 18 / 5 = 3.6 is the average of the series.
= AVERAGE (H7: H11 )
= MEDIAN (H7: H11 )
= SUM ( H7 : H11 ) / COUNTA ( H7 : H11)
The median function uses of the arguments:-
The arguments can contain numbers, cell references, formulas, and other functions, or maybe 1 to 255 arguments (Like number 1, Number 2, Number 3, Etc.) of the Median function mentioned below.
= MEDIAN ( number 1, Number 2, Number 3, Etc / Range Value )
Things to Remember while Using Median Function
- This function only applies numeric values and dates; it will not work on any text value type. Arguments with text or error values that cannot be translated into numbers cause errors.
- If there is an even number of values in the dataset, the average of the two middle values is returned.
- If there is an odd number of values in the dataset, exactly the middle numbers of the set are returned.
- In current versions of Excel (Excel 2007 and later), the function can only accept up to 30 number arguments, but you can provide up to 255 number arguments to the Mode function in Excel 2003.
- Cells with zero values (0) are added to calculations.
Recommended Articles
This has been a guide to Median Function in Excel. Here we discuss the Median Formula in Excel and How to use the Median function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
