The MEDIAN function in Google Sheets is useful to find the middle value in a numeric dataset.
Table of Contents
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.
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:
How to Use MEDIAN Function in Google Sheets
Let’s see how to use MEDIAN function in Google Sheets step-by-step.
- To start, simply click on any cell to make it the active cell. For this guide, I will be selecting D2, where I want to show my result.
- Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘
median
‘ (or ‘MEDIAN
‘, whichever works).
- Now you should find that the auto-suggest box will pop-up with the name and description of the function
MEDIAN
.
- After the opening bracket ‘(‘, you have to add the variables. Remember that you can add many variables, but only the first one is required, and it can be a range of cells as well.
- In this example, the numbers (the ages of the team players) are all in one column so that we can define these numbers as one range of cells, namely B2:B10. Let’s highlight this range with your mouse to add it to the function.
- Finally, press the Enter key to add the closing brackets ‘)‘ and to complete the function. Great! You can see the result of the median calculation.
That’s pretty much it! You can now use the MEDIAN
function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂
