How to Use MEDIAN Function in Google Sheets [Step-By-Step]

The MEDIAN function in Google Sheets is useful to find the middle value in a numeric dataset.

The Anatomy of the MEDIAN Function
A Real Example of Using MEDIAN Function
How to Use MEDIAN Function in Google Sheets

The median is a very commonly used average value. The median of a data set means that half of the numbers in the data set are greater, and half of the numbers are less than the median.

Let’s take an example.

Say you have a sports team and you have a list of the players’ ages. 🏀

It contains diverse values: 23, 28, 23, 24, 23, 26, 24, 31, 23.

It’s easy to find the median if you first put the numbers in order from least to greatest. So to calculate the median manually, let’s sort these values in ascending order: 23, 23, 23, 23, 24, 24, 26, 28, 31.

There are nine numbers in the list, which mean the middle one will be the fifth number: 23, 23, 23, 23, 24, 24, 26, 28, 31. The median age of the team is 24. Half of the team is younger than 24 years old, and half of them are older.

This is the easiest example because the number of values in the list is an odd number, and there is always one number that is in the middle.

Let’s see another example. Take the case of a data set where the number of values is an even number.

In this situation, there is no middle number, but there are two middle numbers. The median of such a data set is the mean (the usual average) of the middle two numbers. The median of the numbers 23, 23, 24, 26, 28, 31 is the mean of the two middle numbers (24 and 26), which is 25.

It’s pretty simple. But how do we calculate the median with large data sets?

The MEDIAN function in Google Sheets returns this value automatically.

Let’s dive into real-business examples and see how we can write our own MEDIAN function to calculate the median value of any data set.

The Anatomy of the MEDIAN Function

The syntax of a function means the way we have to write it in order to work. The syntax for the MEDIAN function is:

=MEDIAN(value1, [value2], …[value30])

Let’s dissect this thing so we’d better understand what each term means:

= the equal sign is required to start every function in Google Sheets.
MEDIAN is our function. We need to add the variables into it to make it work.
value1 is the only required variable of the function. It’s the first value or range of cells to consider when calculating the median value.
value2 , value3, … value30 are optional values. You can add up to 30 additional values or ranges to consider when calculating the median value.

Basically, there are two main ways to write your MEDIAN function.

You can use actual numbers (values). In this case, the function calculates the median of these numbers:

=MEDIAN(1, 5, 10, 11, 12)

Although it is much more common and useful to put one or more range(s) of cells into the function to include the whole list(s) of numbers in your calculation:

=MEDIAN(B2:B10, B14:B16)

⚠️ Now a few notes when writing your own MEDIAN function

The value1, value2, … variables can contain simple numbers, cell references, or ranges of cell references.
The maximum number of variables you can include in the function is 30. However, this only applies to the number of variables. Eventually, you can add way more actual numbers to include in the calculation of the median. You can use infinite numbers if your function uses ranges of cells.
Anything you include in the calculation is sorted numerically by the function, from the lowest to the highest value. You don’t need to sort your values manually.
The MEDIAN function automatically excludes text values and empty cells from the calculation.
On the contrary, it does NOT exclude cells that contain the value 0. These cells will be included in the list of numbers when finding the median. Certainly, the median changes when additional zeros are present in the data set! While the median of the numbers 10, 12, 15 will be 12 (because it is the middle number), the median of the numbers 0, 10, 12, 15 will be 11 (because it is the mean of the two middle numbers, 10 and 12).
Some of the median numbers are not whole numbers, but decimal values ending with a 5 (for example, 4.5 or 12.5). If you think about the way the median is calculated, you can confirm that it is totally normal to get a decimal number as the median in some cases.

A Real Example of Using MEDIAN Function

Take a look at the example below to see how the MEDIAN function works in real-life sheets. Let’s get back to our example, where we would like to calculate the median age of our sports team.

MEDIAN Function in Google Sheets

The above image shows how to use the MEDIAN function in Google Sheets to calculate the middle value of the ages of the team players. The function is as follows:

=MEDIAN(B2:B10)

Here is what this function does:

As you can observe in our data set, the list of ages of the team players are in column B. We need to include these values in our calculation.
To start, we select an empty cell where we will write our formula. For this guide, we picked cell D2.
Then, we used our MEDIAN formula to calculate the median age of the team.
We supplied our formula with the necessary attributes, such as the range of cells where the ages of the team players are written (B2:B10).
We don’t need to worry about sorting the list of numbers because it is done automatically by the function.
There are empty cells in the list. As I have noted, the function ignores and doesn’t include these cells in the calculation.
When we hit on the Enter key, it gives us an answer of 25, which is the median age of the team.

Super easy, right?

Try it out by yourself. You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:

Make a copy of example spreadsheet