MEDIAN Function in MS Excel

MEDIAN Function in MS Excel

4 mins read1.3K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:29 IST

The article covers the MEDIAN function in MS Excel. It is in continuation to the MS Excel basic functions series.

2022_03_Median-function-1.jpg

The MEDIAN function is one of the most popular mathematical functions in MS Excel. It allows you to calculate the value that is in the middle of a set of numbers or values. In this Excel function, if the number of values ​​to be analyzed in the set is even, the average of the middle numbers is calculated. We will understand this through the examples below.

The arguments to the MEDIAN function can be numbers, ranges with numeric values, arrays, or cell references. The maximum number of arguments that can be placed in the MEDIAN function depends on the version of Excel you are using. In Excel 2007 you can enter up to 255 arguments.

Content 

How to Use the MEDIAN Function in Excel

Using the MEDIAN or MEDIAN function in Excel is very simple. You just have to write the formula  =MEDIAN in a cell of your spreadsheet where you want the median value to appear and then select the cells whose value you want to calculate.

Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more

MEDIAN Formula

=MEDIAN(number1, [number2], …)

To use the MEDIAN function, the following parameters must be indicated:

  • number1 (required): The first number to evaluate.
  • number2 (optional): The second number to evaluate and up to 255 optional numbers.
Argument Type Example Explanation
Cell Reference =MEDIAN(A1, B1) Median between the values ​​in cells A1 and B1
Reference to Ranges =MEDIAN(A1:A10) Median between values ​​from cells A1 to A10
Reference to Columns =MEDIAN(C:C) Median between values ​​of the cells of column C
Row Reference =MEDIAN(1:1) Median between values ​​of the cells of row 1
Numbers =MEDIAN(100, 200, A1) Median between 100, 200 and value of cell A1
Multiple Columns/Rows =MEDIAN(A1:A10, C1:C10) Median between cell values ​​from A1 to A10 and from C1 to C10

Important to Note

  • If the total number of elements in the data set is even, MEDIAN calculates the average of the middle numbers. 
  • If the total number of elements is an odd number, then the MEDIAN will take the value located in the center of the list.
  • If the array or reference argument contains text, logical values, or empty cells, these values ​​are ignored; however, cells with the value zero will be included.
  • Arguments that are error values ​​or text that cannot be translated into numbers cause errors.
  • If you want to include logical or text values, use the MEDIAN function.
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.

Let’s understand how MEDIAN function determines values –

Example 1 – Calculating Median in an ODD Range in Excel

Here we have a data set of products from different categories, along with their pricing. To figure out the median value of the dataset, we will apply the MEDIAN function. In this data set, we have an ODD number (13) of columns.

Start typing =MEDIAN in the cell where you want the result to appear, here B15.

Now choose the cells for which you want the median value. We are selecting the complete dataset set, which is B2:B14.

Press Enter

You have the median value. In this case, we have entered 13 specific values ​​and we can see how the median has returned the value that is “in the middle” of the data set.

Example 2 – Calculating Median in an EVEN Range in Excel 

We are using the same data set as above, with just one added row, A14:B14.  

In this data set, we have an EVEN number (14) of columns.

Now again, start typing =MEDIAN in the cell where you want the result to appear, here B16.

Now choose the cells for which you want the median value. We are selecting the complete dataset set, which is B2:B15.

Press Enter to have the median value.

The value you achieved has a decimal. The explanation is –

=MEDIAN(B2:B14) 

Median of the 13 numbers in the range B2:B14. Because there are 13 values, the 7th is the median.

=MEDIAN(B2:B15)

Median of the 14 numbers in the range B2:B15. Because there are 14 numbers, the median is the point between 7 and 8 numbers.

Hope this article helped you understand the MEDIAN function in MS Excel. Keep following us for more such MS Excel tutorials.


Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments