MEDIAN Excel Function

Median Function in Excel

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 numbers is the number in the middle of the set. MEDIAN measures central tendencyCentral Tendency Central Tendency is a statistical measure that displays the centre point of the entire Data Distribution & you can find it using 3 different measures, i.e., Mean, Median, & Mode.read more, which is the location of the center of a group of numbers in a statistical distribution.

MEDIAN Formula in Excel

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 to be calculated.

At least one number must be provided. Subsequent numbers are optional. A maximum of 255 numbers can be provided in the median function. The input can be either numbers, names, arrays, or cell references that contain numbers. Any logical value and text representations of numbers typed directly as input are also counted by the Median function.

Output:

MEDIAN calculates the median of the given set of numbers. Exactly half of the input numbers have values that are greater than the median, and half of the numbers have values that are 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. Suppose there is a total of six numbers given as input, then the MEDIAN will return the average of the 3^rd and 4^th numbers. MEDIAN function first rearranges the input numerical values in ascending order and then identifies the middle value.

Illustration

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

To calculate the median function, you could use the following MEDIAN Formula:

= MEDIAN( B3:B7 )

The MEDIAN formula will return the middle value, i.e., 4.

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

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

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.

which is 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 5^th value, i.e., 5, instead of giving 14 as an output.

Output:

How to Use the MEDIAN Function in Excel?

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

The Median function in Excel gives a measure of the central tendency or average value and is most suitable when the data is skewed or include exceptionally high or low values. MEDIAN is the most suitable measure for data classified on an ordinal scale. MEDIAN function in Excel can be used to find the median sales, revenue, or expenses.

You can download this MEDIAN Function Excel Template here – MEDIAN Function Excel Template

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 enter. It will return the median.

Example #2

Suppose you have the height of 12 students given in cell C4:C15. 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 different months, as shown below.

Now, you want to calculate the median petrol prices for both each month and then compare the prices 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 Enter. It will give the median for Aug, i.e., 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))

which will return 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 as above average and, otherwise, the student will be considered 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 Enter. It will return the performance for the 1^st student.

Now, you can simply 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 is 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, i.e., 15000.

Things to Remember

Median function in Excel gives the median of any given set of numbers.
At least one number should be provided as an input to the Median Excel function.
A maximum of 255 numbers can be provided as an input to the median Function in Excel.
The input can be either numbers, names, arrays, or references that contain numbers.
Logical values are also counted as numbers by the median Function in Excel.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST