How to use MEDIAN Function in Excel

Median function is categorized under the statistical function, This MEDIAN function returns the median of the numbers provided. This is the number in the middle of set of numbers, separating the higher half of its value it is the central aspect of the data set arranged in order of magnitude

The values supplied as arguments do not need to be sorted in any particular in order for the function to work.

Syntax:= MEDIAN (number1, [number2], …)

The MEDIAN function syntax has the following arguments:

1. Number1 (required argument) – The number arguments are a set of one or more numeric values (or arrays of numeric values), for which we wish to calculate the median.
2. Number2 (optional argument)

Example: Let’s look at some Excel MEDIAN function examples and explore how to use the MEDIAN function as a worksheet function in Microsoft Excel:

Example 1: In the study of age groups of students used data from a randomly selected group of students in the university. The task is to determine the average age of students.

Syntax: =MEDIAN(B2:B17)

Result: 23

That is, there are students in the group whose age is less than 23 years and more than this value.

Example 2:

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.

Syntax: =MEDIAN(B2:B21)

Result: 8245

Based on the Excel spreadsheet above, the following MEDIAN examples would return:

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

Syntax: = INDEX(B23:D23,MATCH(MAX(B24:D24),B24:D24,0))

Result: January

Example 3:

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

Now, you want to compare the marks with the median marks obtained. If the marks obtained is 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:

Syntax: =IF(B2>=MEDIAN( $B$2:$B$17),”Above Average”,”Below Average”)

Result: Below Average

Based on the Excel spreadsheet above, the following minifs examples would return:

Note:

• If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second formula in the example.
• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

• Arguments that are error values or text that cannot be translated into numbers cause errors.

• Average     which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median     which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

• Mode     which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.