MEDIAN Function in Excel – A Statistical Parameter

In our previous articles, we implemented the MODE and FREQUENCY Function in excel. The MEDIAN Function is an essential Statistical Function. Let’s see the implementation of the MEDIAN Function in Excel.

When to Use MEDIAN Function in Excel?

The MEDIAN function returns the middle value in a set of values. Median refers to the middle value when the data is sorted in increasing or decreasing order. It can be used to find the median salary, budgeting, or business calculations. Median acts as a central tendency that also helps in qualitative analysis. Unlike the average ( mean ) value, the median does not consider the extreme values of the data set.

Working of MEDIAN Function

The function works on the sorted list of values. We can divide the working of MEDIAN Function into two forms:-

• N is Odd:- When the total number of items in the list is odd, then the middle term is the median of the data. For Example, when N=5, then the median is (N+1)/2 th term i.e 3rd term is median
• N is Even:- When the total number of items in the list is even, then the average of two central terms is the median of the data. For Example when N=6, then the average of N/2 th term and (N/2) + 1 th term is the median. I.e average of the 3rd and 4th terms will be the median of the data.

Syntax and Arguments

=MEDIAN(number1,[number2]……)

The following points explain the function arguments required for the working of the MEDIAN formula:-

• number1 – This supplies the numerical value. It can be a number, cell reference, range of cells, or a named range.
• [number2] – This is an optional argument and works the same as number1

We can supply a maximum of 255 such number arguments out of which only the first number1 argument is mandatory to specify.

Points to Remember about the MEDIAN Function in Excel

The following points about the MEDIAN formula must be kept in mind before its actual implementation.

• We can pass the list of values in an unsorted way. The function automatically returns the result on the basis of sorted form of a list.
• When N is odd, the (N+1)/2 th term is median.
• When N is even, the mean of N/2th term and (N/2)+1th term is the median.
• The MEDIAN function ignores the empty cells, cells containing text strings, and Logical Values (TRUE and FALSE).
• The formula returns a #NUM! error when the supplied list does not contain any numerical value.
• Zero is included in the median calculations.
• We can merge the MEDIAN formula with the IF function to get the Conditional Median.

Examples to Learn MEDIAN Function

Here we will cover some practical parts to learn the MEDIAN function implementation.

Example 1 – Median for Odd Frequency for Sorted Values

Let us suppose we have the salaries of five employees of a company. Here the frequency is odd since there are five employees’ data.

Use the following MEDIAN Formula in cell B7.

=MEDIAN(B2:B6)

As a result, the formula returns MEDIAN salary as 45000.

Explanation – The salaries in the range B2:B6 are already sorted in ascending order. Since N=5 is odd, the median will be the (N+1)/2 th term. (5+1)/2=3rd term. According to the sorted list in B2:B6, the third term is 45000. Consequently, the function returns the median salary as 45000.

Example 2 – Finding Median for Even Frequency for Unsorted list

Let us suppose we have the following set of values:-

Here N=6 which is even. Use the following MEDIAN formula in cell A8 to get the median value.

=MEDIAN(A2:A7)

As a result, the formula returns the median as 52.

Explanation:- Here N=6 is an even number. So the median will be the average of the 3rd and 4th terms. The 3rd and 4th terms are not 34 and 32 as the list is not sorted. The sorted list would be:-

When we sort the list, the (N/2)th term and (N/2)+1th term came out to be 45 and 59. The average of these two terms is the median value.

This brings us to the end of blog.

Thank you for reading.