MEDIAN Excel Function

The MEDIAN function in Excel gives the median of any given set of numbers and is categorized as a statistical function. The median of any given number is the number in the middle of the set.

The MEDIAN measures central tendency, which is the location of the center of a group of numbers in a statistical distribution.

For example, =MEDIAN(5,6,7,8,9) returns 7.

Likewise, we can use MEDIAN function to find the results.

Below is the MEDIAN formula in Excel.

Arguments used for MEDIAN Formula:

• number_1, number_2, … , number_n: Set of numbers or cell references denoting numeric values for which the median is calculated.

We must provide at least one number. Subsequent numbers are optional. We can provide a maximum of 255 numbers in the MEDIAN function. The input can be numbers, names, arrays, or cell references containing numbers. Any logical value and text representations of numbers typed directly as the MEDIAN function counts input.

Output:

The MEDIAN function calculates the median of the given set of numbers. Half of the input numbers have values greater than the median, and half of the numbers have values less than the median. If there is an even number of inputs, then the MEDIAN function calculates the average of the two numbers in the middle. For example, suppose there are six numbers given as input, then the MEDIAN function will return the average of the third and fourth numbers. The MEDIAN function first rearranges the numerical input values in ascending order and then identifies the middle value.

Suppose you want to find the median of numbers {2, 3, 4, 5, 6}. These numbers are given in cell B3:B7.

First, to calculate the median function, we should use the following MEDIAN Formula:

=MEDIAN(B3:B7)

Next, the MEDIAN formula will return the middle value, 4.

Now, instead of cell references, you could give the input values directly as:

=MEDIAN(2, 3, 4, 5, 6)

Now, this MEDIAN formula in Excel will return the same output.

If you use an even number of values as an argument, such as {2, 3, 4, 5, 6, 7}, the MEDIAN formula will output the average of the middle two values- 4 and 5.

Next, it will return the result as 4.5.

Suppose you give the nine arguments {3, 8, 4, 12, 14, 5, 1, 2, 10} which are not arranged in ascending order, MEDIAN will itself rearranges it in ascending order: {1, 2, 3, 4, 5, 8, 10, 12, 14} and then return the fifth value, 5, instead of giving 14 as an output.

Output:

The MEDIAN Excel function is very simple and easy to use. Let us understand the working of the MEDIAN function by some examples.

The MEDIAN function in Excel measures the central tendency or average value. It is most suitable when skewed data includes exceptionally high or low values. The MEDIAN is the most appropriate measure for data classified on an ordinal scale. For example, we can use the MEDIAN function in Excel to find the median sales, revenue, or expenses.

Example #1

Suppose you have the sales data for different products of your company. The data is given in cell B4:B17.

Now, you want to calculate the median sales. To do so, you can use the MEDIAN formula in Excel:

=MEDIAN(B4:B17)

And press the "Enter" key. It will return the median.

Example #2

Suppose you have the height of 12 students given in cell C4:C15. Then, you want to calculate the median height of the students.

To calculate the median height, you can use the MEDIAN formula in Excel:

=MEDIAN(C4:C15)

It will return 167.

Example #3

Suppose you have the petrol prices of different cities in the country for two months, as shown below.

You want to calculate the median petrol prices for each month and then compare them based on their median values.

To calculate the median prices for Aug, you can use the MEDIAN formula in Excel:

=MEDIAN(C4:C17)

And press the "Enter" key. It will give the median for Aug - 82.42.

Similarly, you can find the median for Sep using the MEDIAN formula in Excel:

=MEDIAN(D4:D17)

It will return 82.365

Now, to find which month had the larger median value, you can use the INDEX:

=INDEX(F4:F5,MATCH(MAX(G4:G5),G4:G5,0))

It will return the result as Aug.

Example #4

Suppose you have the marks obtained by students in a class. The marks are given in cell D4:D23.

Now, you want to compare the marks with the median marks obtained. If the marks obtained are greater than the median, the student will be considered above average. Otherwise, the student will be regarded as below average.

To do so, you can use the following MEDIAN formula:

=IF(D4>=MEDIAN($D$4:$D$23),“ABOVE AVERAGE”, “BELOW AVERAGE”)

And press the "Enter" key. It will return the performance for the 1st student.

Now, you can drag it to the rest of the students to get the performance of each of the students.

Let us see the MEDIAN formula in Excel in detail.

• MEDIAN($D$4:$D$23) will calculate the median of the marks obtained by the students. Here, the median is 74.4.

• IF(D4>=MEDIAN($D$4:$D$23),“ABOVE AVERAGE”, “BELOW AVERAGE”) means that if the marks obtained by the student are greater than the median, it will return “Above Average,” else it will return “Below Average.”

Example #5

Suppose you have the monthly salary of a department of your company. The salary is given in cell B4:B13.

Now you are interested in calculating the central tendency of the given salary. You have decided that if the standard deviation is greater than one-third of the mean, you will calculate the median; else, you will calculate the mean.

To do so, you can use the MEDIAN formula in Excel:

=IF(STDEV(C4:C13)>(AVERAGE(C4:C13)/3),MEDIAN(C4:C13),AVERAGE(C4:C13))

In this case, the standard deviation is 29959, and the mean is 28300, which clearly shows that the data has a very high standard deviation. The standard deviation, in this case, is greater than one-third of the mean; thus, it will report the median value, 15000.

• We can easily find the middle value of any data using MEDIAN function in excel.
• It is an inbuilt statistical function.
• If the data has even number of values, the MEDIAN function will return the average of the two middle values.