Excel: median function: explained with practical examples

The Excel median function calculates the middle number (median) in an existing group of numbers. In contrast to the mean value, the median does not give you the average, but the central tendency of a number series ordered by size. Learn here how to use the median function in Excel.

What is the median?

In statistics, the median is a special threshold value known as the quantile, which divides a given series of numbers into two halves. The numbers or values are measured in ascending order. The median shows the numerical value in the middle position of the examined series of numbers.

Excel median function: formula syntax

The median is a practical Excel function to obtain concrete central values. To determine the median in Excel, use the following formula syntax:

=MEDIAN(number1;number2;…)

The formula syntax of the Excel median function consists of the necessary, numeric variable “Number1” and optional, subsequent variables that specify from which number series the center is to be calculated. The variables “Number1”, “Number22 etc. can be numbers as well as cell references.

Here’s how to use the median function

If you want to insert the median of a group of numbers in Excel, double-click in a cell and enter “=MEDIAN(A1:E1)”, for example. This will give you the median number of the numerical values in cells A1 to E1. Alternatively, you can integrate the median formula directly in a tabular overview as a column or in a row.

Example:

  • If you are looking for the median in an odd number of values in cells A3 to A7, enter “=MEDIAN(A3:A7)”. This will give you the median 3.
  • If you are looking for the median in an even number of values in A3 to A8, enter “=MEDIAN(A3:A8)”. In this case, you will get the median 3.5, i.e. the value between the numbers in the third and fourth place.

Using Excel median – what you need to know

The median function is able to calculate the mean value from 1 to 255 (maximum column width in Excel). However, you need to ensure that the arguments are numeric values, i.e. ordinally scaled variables. The function ignores text, names, matrices, references, and logical values that do not contain numbers. The median of TRUE/FALSE values and formulated numbers, on the other hand, can be determined by the function.

Difference to mean and modal value

The median reflects the central tendency of ascending numerical values in their statistical distribution. To determine a central tendency, you have three options:

  • Median: Specifies the median in a hierarchically ordered group of values.
  • Mean: Gives the arithmetic mean by dividing the sum of the given numbers by the amount of numbers.
  • Modal: Displays the most frequently represented number in a numeric group.

If list values are evenly distributed, the median may resemble the mean or modal value. If the values are unevenly distributed, the median usually deviates from the mean and modal value.

Even and uneven number of values

The number of values must be distinguished between an even and odd number of values:

  • If the number of values in a list is uneven, the median specifies the middle position.
  • If the number of values in a list is even, the median value is represented by the average of the two numbers that form the center (upper and lower median).

When is the Excel median used

The median is used when the central value of data with ordinal, interval, or ratio scaling is to be calculated. Common areas of application are:

  • To determine the central value of samples in descriptive statistics.
  • To determine mean positions in probability distributions and random variables in probability theory.
  • As a measure of robust estimation procedures for unknown value distributions in mathematical statistics.

Two practical examples of the Excel median function

To understand the practical utility of the median function, consider the following practical examples of use:

Example 1: Median for income calculation

The central value of annual income among a group of eight persons is to be determined. Depending on the distribution of income sizes, the mean may determine the average income, but it may not give a realistic picture if 80 percent earn below a certain threshold and 20 percent earn above it. This is where the mean comes into play.

 

A

B

1

Person

Annual income in $

2

Silvia

44,000

3

Peter

500,000

4

Karim

31,000

5

Azra

33,000

6

Lin

75,000

7

Markus

900,000

8

Claudia

25,000

Median function

=MEDIAN(B2:B8)

44,000

The median reflects the middle of the income ratios, i.e. the annual income of the “average” employee. The mean value, on the other hand, would be $229,714 – the average annual income in this case, which can be understood as a distorted income ratio.

In the case of an even number of eight people, the median is determined from the average of the lower and upper median in each case (here: B3 and B6):

 

A

B

1

Person

Annual income in $

2

Matthias

10,000

3

Silvia

44,000

4

Peter

500,000

5

Karim

31,000

6

Azra

33,000

7

Lin

75,000

8

Markus

900,000

9

Claudia

25,000

Median function

=MEDIAN(B2:B9)

38,500

Example 2: Median of a score sheet

When determining the tendency of school grades, the median helps calculate the middle of a group of grades ordered by size:

 

A

B

1

Student

Grade

2

Klaus

3

3

Emma

1

4

Egane

2

5

Abebi

3

6

Kenan

1

7

Anna

5

8

Yasi

4

Median function

=MEDIAN(B2:B8)

3

Note

The median can be applied without Excel, of course. Here, the numbers of a group have to be arranged in ascending order and the median number (in the case of an odd number) or the average of the two median numbers (in the case of an even number) has to be determined.


Wait! We’ve got something for you!
Have a look at our great prices for different domain extensions.


Enter the web address of your choice in the search bar to check its availability.
.org
$1/1st year
then $20/year
.com
$1/1st year
then $15/year
.info
$1/1st year
then $20/year
.me
$1/1st year
then $20/year