How to calculate the median in Excel

Excel’s median function calculates the middle number (median) of a group of numbers. Unlike the mean, the median doesn’t provide the average. Instead, it represents the central point of a sequence of numbers.

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 set of numbers.

What is the syntax of the Excel median function?

The median is a practical Excel function used to obtain precise central values. To find the median in Excel in Office 2016 onwards and Microsoft 365, use the following syntax:

=MEDIAN(number1,number2,…)

The syntax of the Excel median function consists of numeric variables. The first variable, number1, is mandatory, however, the subsequent variables are optional. The additional variables are used to specify which values should be included when calculating the central value. You can use numbers or cell references for the variables.

How to use the median function

If you want to find the median of a group of numbers in Excel, first double-click a cell and enter =MEDIAN. Next, you need to enter the numeric variables in brackets. To find the median number of the numeric values in cells A1 to E1, for example, enter =MEDIAN(A1:E1). You can also designate an entire column or row to calculating the median of different sets of values in your table.

What to keep in mind when using Excel MEDIAN

The median function can calculate the median value for up to 255 numbers. However, you need to ensure that the arguments are numeric values, i.e. ordinally scaled variables. This function ignores text, names, matrices, references and logical values that do not contain numbers. The median of TRUE/FALSE values and calculated values, on the other hand, can be determined by the function.

How does the median differ to mean and mode values?

The median reflects the central tendency in a set of numerical values arranged in ascending order. In You can determine the central tendency of a data set using three different approaches:

• Median: Specifies the middle value in a hierarchically ordered group of numbers
• Mean: Gives the arithmetic average by dividing the sum of the given numbers by the total amount of numbers
• Mode: 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.

Calculating even and uneven sets of values

In a dataset, you will have either an even number of values or an odd number:

• If the number of values in a list is uneven, the median specifies the middle position.

Example: Let’s assume you have the numbers, 1,2,3,4 and 5 in cells A3 to A7. Here, you have an odd number of values. When you enter =MEDIAN(A3:A7), you will get the median 3.

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

Example: In cells A3 to A8, you have the numbers 1,2,3,4,5 and 6. To determine the median from this even number of values, enter =MEDIAN(A3:A8). The result will be 3.5, i.e. the value between the numbers in the third and fourth place.

How to calculate the median in Excel

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

• Determining the central value of samples in descriptive statistics
• Determining 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

Excel MEDIAN examples

To understand how to use the median function, we’ve included some practical examples below:

Example 1: Finding the median income for a group of people

Here, we’re going to determine the middle value for the annual income among a group of eight people. Depending on the distribution of income sizes, the mean may determine the average income, but it may not give us a realistic picture if 80 percent earn below a certain threshold and 20 percent earn above it. This is where the median can help.

The median represents the middle value of the income distribution, more accurately reflecting the annual income of a typical employee. On the other hand, the average annual income in this scenario would be £229,714, which can be seen as a skewed representation of average employee income.

If there is an even number, the median is determined from the average of the lower and upper median. In this case, B3 and B6:

Example 2: Finding the median mark for a class

When determining the central tendency of school marks, we can use the median function to help calculate the middle value of a group of marks. To calculate the median, we’ve changed the letter marks to numbers with 1 corresponding to an A, 2 corresponding to a B, 3 corresponding to a C, 4 corresponding to a D, and 5 corresponding to an E.