The MEDIAN is one of the important functions in Excel to analyse data sets. Whenever you need to find a middle value of a range, this function helps you do that. It is more efficient than the AVERAGE function because it eliminates the heavy influences of extreme values on it. It also helps you understand the central tendency of a set of data. We are going to learn about the purpose of the MEDIAN function, its syntax and give some examples on how you can apply it in practical ways.
What is the MEDIAN Function?
The MEDIAN function returns the median, or middle number, of a specified data range. If you have an odd number of data points, the median is the middle value; if you have an even number of data points, it’s the average of the two middle values. While having the same general characteristics as the average, this function will be useful for you when you need to analyse data distributions. It is less affected by outliers than the average; therefore, it can be applied to datasets containing some very high or very low values.
Syntax of the MEDIAN Function
The syntax for the MEDIAN function is pretty obvious:
=MEDIAN(number1, [number2], …)
number1: this is the first number or range of cells for which you would like to compute the median.
number2: Are not required. You may insert numbers or ranges of cells if needed.
You can feed in cell references, ranges, or a mix of the two. Excel accepts up to 255 arguments, but most applications involve just one or two ranges.
Key Points to Remember
Non-numeric values: The MEDIAN function ignores cells that contain text, logical values, or empty cells, so it looks only for numbers.
Ignore Zeros: If you have zeros in your data range, they are considered as numbers. You may need to avoid this by using an array formula or zero-removing at the data level before you will be able to apply a MEDIAN formula.
Formula:Simple Median
Example 1:
Suppose we wanted to find the median of a list of sales data in cells A1 through A7. This is how we would do it:
=MEDIAN(A1:A7)
Assume the values in A1 are:
Rs. 50,000
Rs. 75,000
Rs. 90,000
Rs. 100,000
Rs. 120,000
Rs. 150,000
Rs. 200,000
The formula will return Rs. 100,000 as the median, which is the middle number in this list.
Example 2:
Median with an even number of values
Apply the same range but now with only six values:
=MEDIAN(A1:A6)
Suppose the values are:
Rs. 50,000
Rs. 75,000
Rs. 90,000
Rs. 100,000
Rs. 120,000
Rs. 150,000
The function will give you Rs. 95,000, that is half way between the two middle numbers: Rs. 90,000 and Rs. 100,000.Â
This is so because in the case of having an even number of values, the median is obtained as the mean of the two middle values.
Example 3:Â
Multiple Ranges for the MEDIAN Function
The multiple range option for the MEDIAN function is in use. Suppose you have one set of sales data in A1 and another set in B1, so by selecting both ranges it will calculate the median of both as follows:
=MEDIAN(A1:A5, B1:B5)
If the values in A1 are:
10, 20, 30, 40, 50
And in B1:B5 are:
60, 70, 80, 90, 100
The combined sorted list is 10, 20, 30, 40, 50, 60, 70, 80, 90, 100. Thus, the median is 55, the average of 50 and 60, the two middle numbers in the sorted list.
Example 4:
Median with Criterion
Although it does not support criteria directly, the MEDIAN function in Excel can at times be used with other functions to build a conditional median. For instance if you need the median of only values greater than 50, use an array formula as follows:
=MEDIAN(IF(A1:A10 > 50,A1:A10))
To use this formula in older versions of Excel, you would need to enter it with Ctrl+Shift+Enter to make it an array formula. With Excel 365 and Excel 2019, you can press Enter since they handle arrays by default.
Example 5:Â
Median of Non-Zero Values Only
To find the median of a set of numbers where you would like to ignore zeros, you change the formula to ignore zeros by using an array formula:
=MEDIAN(IF(A1:A10 <> 0, A1:A10))
This formula will take into account only non-zero values within the range A1.
Practical Applications of the MEDIAN Function
Analysing Salary Distributions: The best way to get a sense of typical compensation levels might be the median salary in HR, because averages are misleading if there are extreme values for a few high or low salaries.
Household Income Analysis: Median income is the most common statistic in economics because it is relatively less affected by extreme values and provides a better measure of central tendency for income data.
Data cleaning: the MEDIAN function can be used to find typical values in a data set and mark outliers for later checking.
How to use median effectively
Sorting isn’t necessary: The MEDIAN function doesn’t require your data to be sorted; she will find that middle value regardless of what order you present your data.
Combining MEDIAN with Other Functions: There are cases where pairing MEDIAN with other functions like IF, SUMPRODUCT, or even FILTER can allow more directed analysis meeting criteria-based requirements.
The MEDIAN function in the application of Excel can be used to determine the middle value that holds within the dataset.Â
Since this function, unlike the average, is used for the giving of a stronger measure of central tendency-where especially outliers are present-making your data analysis more accurate and insightful while using the MEDIAN function for various ranges and criteria.Â
You’ll learn how to compute the MEDIAN function as you read how to analyse a set of numerical data like the salaries or sales of some companies to make better, data-based decisions.