Watch our step-by-step video tutorial:
There are certain statistical metrics that are often used to analyze and get a basic idea about a given set of data. Some of these metrics include the mean, median, mode, and standard deviation.
Among these metrics, the median gives us an idea about the central tendency of the data.
The Google Sheets MEDIAN function helps find the median of a given list of numbers. The function is a versatile one that can be used with a variety of input types.
In this tutorial, we will show you how to find the median in Google Sheets, with different forms of input.
What Does the Median Tell Us About a Dataset?
The median of a dataset, like the mean, gives us an idea about the distribution of the data. It tells us at what point the dataset is centered, and also lets us know if the data is skewed.
The median, however, is different from the mean, in that, it calculates the central value of the dataset, unlike the mean.
Moreover, the median requires the list to be sorted, which is not a requirement when finding the mean.
What Does the Google Sheets MEDIAN Function Do?
The MEDIAN function in Google Sheets returns the central value in a numerical list of data.
If the list has an odd number of data items, the function returns the value at the center. If the list contains an even number of data items, then the function returns the arithmetic mean of the central two values.
The median is especially helpful when your dataset contains outliers, because in such cases, the mean does not provide a very good representation of the data.
In fact, if the median of your data is very different from the mean, it indicates the presence of outliers.
How to Find the Median in Google Sheets
To find the median of a dataset in Google Sheets, you can use the MEDIAN function.
The general syntax for this function is as follows:
Here, the arguments can be any of the following four types:
- A set of values
- A range of cell references
- A combination of values and cell references
- A filtered set of values
To understand how to use the MEDIAN function, let us consider an imaginary set of scores for 6 students:
Finding the Median Using a Set of Values as Arguments
One way to find the median is by specifying the direct numeric values in the MEDIAN formula, as follows:
Here, value1, value2,… each correspond to a value in your sample. So in our example, we can type the following formula in the target cell:
=MEDIAN(67, 32, 44, 95, 34,50)
Note: You do not need to previously sort the data items, since the MEDIAN function automatically does that for you.
Here’s the result you can expect to get:
Note: If you’re entering numeric values as arguments, you can enter a maximum number of 30 entries only. This, however, does not apply if you use the function with a range of cell references as the argument.
If you get a number with too many decimal places, you can round it off to, say, 2 decimal places by selecting the cell, and navigating to Format->Number->Number from the menu.
Instead of direct values, you could also use cell locations. In our example, you could use the MEDIAN function as follows:
=MEDIAN(B2, B3, B4, B5, B6, B7)
This method, though, is quite inefficient. A better way would be to use the range of locations, as explained below.
Finding the Median Using a Range of Locations as Arguments
The most common way of providing arguments to the MEDIAN function is using a reference to the range of cells containing the data.
- Click on your target cell.
- Type: =MEDIAN(
- Select the cells containing values that you want to calculate the MEDIAN for. You will notice that the range of cell locations that you selected will automatically appear in the cell.
- Close the brackets.
- Press the Return key.
Finding the Median Using a Combination of Values and Location Ranges as Arguments
You can also use a combination of direct values along with cell ranges as inputs to the MEDIAN function.
So you can have something like:
Regardless of the types of arguments you use, you will get the same output for the same data:
Finding the Median Using a Filtered Set of Values as Arguments
In some cases, you might need to apply a filter to your data before calculating the median.
For example, consider the following dataset:
Let’s say you want to find the median for only the students of Section A. You can then wrap the MEDIAN function around a FILTER as follows:
Here, condition is the criteria that qualifies if a cell location is eligible to be considered in the median calculation or not.
So in our example, our formula can be:
Here, the second parameter checks if the cell in the range B2 to B7 is equal to “A”. If so, then it considers the corresponding value of column C in the median calculation.
You can expect to get a result as follows when you apply this formula:
Points to Remember When Using the Google Sheets MEDIAN Function
Here are some important points to remember when using the MEDIAN function in Google Sheets:
- You don’t need to sort the data before applying the MEDIAN function, because it automatically takes care of that.
- The function ignores string values, if any, in your list of arguments.
- The function also ignores empty cells. However, it does not ignore cells containing the value 0.
Other Data Analytic Functions
The MEDIAN function is part of a suite of analytic functions offered by Google Sheets.
Some of the other analytic functions that you can use include:
In this tutorial, we explained different ways in which you can use the Google Sheets MEDIAN function to estimate the central tendency of a given dataset. We hope this was helpful.