MEDIAN Excel Function

Article byWallstreetmojo Team
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Median Function In Excel?

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.

MEDIAN Function in Excel

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

Key Takeaways

  • The MEDIAN function in Excel gives the median of any given set of numbers.
  • We have to provide at least one number should be provided as an input to the MEDIAN function in Excel.
  • The maximum number of values which can be provided as an input to the MEDIAN function in Excel is 255.
  • The input can be numbers, names, arrays, or references containing numbers.
  • Meanwhile, the logical values are also counted as numbers by the MEDIAN function in Excel.

Syntax

Below is the MEDIAN formula in Excel.

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

MEDIAN Excel Function Video

 

Illustration

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

MEDIAN Function Example 1

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

=MEDIAN(B3:B7)

MEDIAN Function illustration 1-1

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

MEDIAN Function illustration 1-2

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

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

MEDIAN Function illustration 1-3

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

MEDIAN Function illustration 1-4

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.

MEDIAN Function illustration 1-5

Next, it will return the result as 4.5.

MEDIAN Function illustration 1-6

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.

MEDIAN Function illustration 1-7

Output:

MEDIAN Function illustration 1-8

How To Use The MEDIAN Function In Excel?

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.

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

Examples

Example #1

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

MEDIAN Function Example 1

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

=MEDIAN(B4:B17)

MEDIAN Function Example 1-1

And press the “Enter” key. It will return the median.

MEDIAN Function Example 1-2

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.

MEDIAN Function Example 2

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

=MEDIAN(C4:C15)

MEDIAN Function Example 2-1

It will return 167.

MEDIAN Function Example 2-2

Example #3

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

MEDIAN Function Example 3

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)

MEDIAN Function Example 3-1

And press the “Enter” key. It will give the median for Aug – 82.42.

Example 3-2

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

=MEDIAN(D4:D17)

Example 3-3

It will return 82.365

Example 3-4

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))

Example 3-5

It will return the result as Aug.

Example 3-6

Example #4

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

Example 4

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”)

Example 4-1

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

Example 4-2

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

Example 4-3

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.

Example 5

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))

Example 5-1

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.

Example 5-2

Important Things To Note

  • 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.

Frequently Asked Questions (FAQs)

1. What is MEDIAN function in excel?

One of the statistical functions available in excel is MEDIAN function. It is used to find the middle value of the data.
 
The syntax of MEDIAN function in excel is =MEDIAN(number1,[number2])…)

2. How to calculate using MEDIAN function in excel?

Using MEDIAN function is very simple.
 
For example, consider the below table with values showing the age of various participants. Now, let us learn how to use MEDIAN excel function.

MEDIAN Excel Function - FAQ 2

The steps used to find the middle value using MEDIAN excel function are:
 
• Step 1: First, insert the MEDIAN excel function.

FAQ 2 - Step 1

• Step 2: Next, select the values for which we have to find the MEDIAN (middle value)
 
In this example, we have to select the cell range B2:B8.

FAQ 2 - Step 2

• Step 3: Press Enter key.
 
We can see that the MEDIAN excel function has returned the middle value from the data.

FAQ 2 - Step 3

We can see that the function has returned the middle value.

Likewise, we can use the middle value from the dataset.

3. How Median function will calculate the middle value if the numbers are even?

If the selected data are even in number, the MEDIAN function will return the middle value by calculating the average of the two middle values.
 
For example, consider the below set of numbers 26, 31, 45, 22, 65, 67. We can see that there are total of 6 numbers.
 
Let us see the middle value of the given set using MEDIAN function in excel.
 
The steps to find the middle value of an even number set are:
 
• Step 1: Insert the MEDIAN function and select the cell range A2:A7.
 
• Step 2: Press Enter key.

FAQ 3 - Step 2

• Step 3: We can see that the function has returned the value as 38. It is obtained by calculating the average of the two middle values (45 and 22).

MEDIAN Excel Function - FAQ 3 - Step 3

Likewise, MEDIAN function calculates the middle value of even number set.

This article is a guide to MEDIAN Function in Excel. We discuss the MEDIAN formula in Excel and how to use the MEDIAN function in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *