MEDIAN Function in MS Excel

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

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
• MEDIAN Formula
• Important to Note
• Example 1 – Calculating Median In an ODD Range In Excel
• Example 2 – Calculating Median In an EVEN Range In Excel

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.

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.

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.

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