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.

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.

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

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 centre is to be calculated. The variables ‘Number1’, ‘Number2’ etc. can be numbers as well as cell references.

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.

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.

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.

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 centre (upper and lower median).

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.

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

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.

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

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