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 in order 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 in excel is very simple and easy compared to other functions in Microsoft Excel, which contains lots of 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 numbers of all set 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, then simply find the middle numbers of value in series, and we get the result. For example, we can see that 9 is the exact middle value of the group.
4.8 (8,747 ratings)
View Course
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 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 value. The median then is the average of the middle two numbers in the group – when sorted numerically.
Suppose we talk about the data type, which can be numbers, dates, named ranges, dates, arrays, or references to cells containing digits. Number1 is required; subsequent numbers are optional.
Basically, as we know, the median function and average function are approximately the same, but there is some difference between both the formulas. Let’s understand the mathematical logic.
In the below-given example, we will find the difference between 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, that as per odd numbers, any mathematical calculation of series finally gets the result exactly in the middle value of set as 3.
When we are using the average function, the numbers are added, or the sum of the total set and then their 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 it’s Maybe 1 to 255 arguments (Like number 1, Number 2, Number 3, Etc.) of the Median function below mention.
= MEDIAN ( number 1, Number 2, Number 3, Etc / Range Value )
Things to Remember About the Median Function in Excel
- This function is applicable in only numeric value and dates; it will not work on any type of text value. Arguments that are text or error values and which 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 argument, but you can provide up to 255 number arguments to the Mode function in Excel 2003.
- Cells with zero values (0) are added in 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 –
