MEDIAN Google Sheets Function

What Is MEDIAN Google Sheets Function?

The MEDIAN Google Sheets function is an inbuilt Statistical function that returns the median or the mid-value of the selected numeric values, ignoring any other values such as text, alpha-numeric, blank or empty cells, etc.

Users can use the MEDIAN function in Google Sheets to determine median sales or expenditures. It is more useful when working with skewed data distribution and when the distribution has outliers.

For example, the below table shows different height values in cm.

MEDIAN Google Sheets function Definition 1

We will determine the median for the above dataset using the MEDIAN function. Choose cell D1, enter the formula =MEDIAN(A2:A10) and press โ€œEnterโ€, as shown below.

MEDIAN Google Sheets function Definition 1-1

In the above example, the dataset has nine data points. So, the output is 162 cm, which is the same if the values are in the numeric order or random order.

Key Takeaways
  • The MEDIAN Google Sheets function determines the median of a given set of numeric values. It is not to be confused with the Average function. The function works well for skewed data distribution or when the distribution contains outliers.
  • The MEDIAN() returns results as follows:
    • The average of the two middle numbers of the dataset when the number of data points is even, and the values are in their numeric order.
    • The middle number of the dataset when the number of data points is odd and the values are in their numeric order.
  • We can use MEDIAN() with other Google Sheets functions, such as IF, involving a single or multiple conditions.

MEDIAN() Google Sheets Formula

The syntax of the MEDIAN Google Sheets formula is:

MEDIAN Formula

where,

  • value1: A cell range of numeric values to find the mid-value.
  • [value2, value3, โ€ฆ]: The second set of one or more numeric values.

While the first argument is mandatory, the subsequent arguments are optional. We can enter the arguments as numbers, named ranges, dates, arrays, or references to cells containing numeric values.

How To Use MEDIAN Google Sheets Function?

We can use the MEDIAN Google Sheets Function in 2 ways, namely,

  1. Access from the toolbar.
  2. Enter in the worksheet manually.

Method #1 – Access From the Toolbar

Choose an empty cell for output ๐Ÿกข select the โ€œInsertโ€ tab ๐Ÿกข click the โ€œFunctionโ€ option right arrow ๐Ÿกข click the โ€œStatisticalโ€ option right arrow ๐Ÿกข select the โ€œMEDIANโ€ function, as shown below.

How To Use MEDIAN Google Sheets Function 1

The MEDIAN formula appears as shown below.

MEDIAN Google Formula

Select the argument values as cell references or cell rangeand press โ€œEnterโ€.

Method #2 โ€“ Enter in the Worksheet Manually

  1. Select an empty cell to display the output.
  2. Type = MEDIAN( in the chosen cell. [Alternatively, enter =M or =MED and click the MEDIAN function from the suggestions Google Sheets lists.]
  3. Enter the cell values or references as the argument values.
  4. Close the brackets and press Enter to obtain the logical results in the target cells.

Examples

Let us consider some examples to understand the MEDIAN Google Sheets Function.

Example #1

The data given below consists of mobile units sold each week in January and February. However, the months of January and February have five and four weeks, respectively. Hence, their datasets have five and four values.

MEDIAN Google Sheets function Example 1

The steps to determine the median values for the two months using the MEDIAN() are,

Step 1: Select cell A9, enter the formula =MEDIAN(B2:B6) and press Enter.

MEDIAN Google Sheets function Example 1-1

Step 2: Select cell B9, enter the formula =MEDIAN(C2:C6) and press Enter.

MEDIAN Google Sheets function Example 1-2

The Median of Jan in cell A9 is 1480 and of Feb in cell B9 is 1307.5, as shown in the above images.

  • The dataset has five data points, but not in numeric order. So, the MEDIAN() listed the numbers in the numeric order {1200, 1320, 1480, 1500, 2000} and returned the middle value, 1480.
  • For Feb, the dataset contained four data points in numeric order. So the MEDIAN() took the two middle values, 1250 and 1365, and returned their average, 1307.5.

Example #2

Let us see what the output of the Median formula will be if the data values have a combination of text and numbers.

Consider the below table with the scores of the students who appeared for the mathematics examination. And for those who did not give the test, the score is NA. We will find the median Mathematics score for the given data.

MEDIAN Google Sheets function Example 2

The procedure to apply the MEDIAN in Google Sheets using the Insert tab is as follows:

  • Choose cell F1 ๐Ÿกข select the โ€œInsertโ€ tab ๐Ÿกข click the โ€œFunctionโ€ option right arrow ๐Ÿกข click the โ€œStatisticalโ€ option right arrow ๐Ÿกข select the โ€œMEDIANโ€ function, as shown below.
MEDIAN Google Sheets function Example 2-1
  • The Median formula appears as shown below.
MEDIAN Google Sheets function Example 2-2
  • Here, select the cell range B2:B11 and press โ€œEnterโ€. The output is shown below as 89.
MEDIAN Google Sheets function Example 2-3

Output Observation: First, the MEDIAN() sorts the data points in their numeric order, {70, 80, 86, 89, 95, 98, 100, NA, NA, NA}. As per the definition of the MEDIAN Google Sheets function explained earlier, the function ignores text values. So, in this example, the function takes the seven numbers in column B, excluding the NA values. Since the number of data points is odd, it returns the middle number as the median value, 89 w.r.t their numeric order.

Example #3

Next, we will calculate the MEDIAN() when the data points in the dataset are dates.

The below table shows the monthly stock update for an item for which we will determine the median date and the median stock value for the specific item.

MEDIAN Google Sheets function Example 3

The steps to use the MEDIAN Google Sheets function to get the required data are as follows:

Step 1: Select cell A16, enter the formula =MEDIAN(B2:B13) and press Enter.

MEDIAN Google Sheets function Example 3-1

Step 2: Select cell A17, enter the formula =MEDIAN(C2:C13) and press Enter.

MEDIAN Google Sheets function Example 3-2

Output Observation: The Median of Date in cell A16 is 7/15/2023 and the Median of Item Stock in cell A17 is 1307.5, as shown in the above images.

  • The MEDIAN() considers the dates as numeric values. In the above example, the dataset in column A contains an even number of dates in their numeric order. So, the MEDIAN() takes the two middle date values, 6/30/2023 and 7/31/2023, and returns their average as the median data value, 7/15/2023.

[Please Note: The date should be in the proper date format in Google Sheets to ensure the function returns the accurate median value.]

  • Also, the number of data points in the dataset in column B is even, 12, with the data points not in their numeric order. So, the MEDIAN() takes the data points in their numeric order, {42000, 49000, 50000, 53000, 53000, 54000, 57000, 59000, 60000, 60000, 61000, 68000}, as the input, considers the two middle numbers, 54000 and 57000, and returns their average as the item stock median as $55,500.

Important Things To Note

  • The MEDIAN Google Sheets function requires at least one number as input and can take a maximum argument as well.
  • We can enter the arguments as numeric values, named ranges, arrays, or cell references containing numeric values. The function ignores text values, blank cells, or cell references to logical values. However, the function counts logical values (TRUE and FALSE) provided directly as arguments and cells containing zero values.

Frequently Asked Questions

1) What is an alternate way to insert the MEDIAN Google Sheets function?

Many at times, we cannot remember in which category the โ€œMEDIANโ€ function falls. Then, we can insert the function as follows:
Choose an empty cell ๐Ÿกข select the โ€œInsertโ€ tab ๐Ÿกข click the โ€œFunctionโ€ option right arrow ๐Ÿกข click the โ€œAllโ€ option right arrow ๐Ÿกข select the โ€œMEDIANโ€ function, as shown below.
MEDIAN Google Sheets function FAQ 1
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can also find the Function icon, to insert the MEDIAN function by following the path shown below.

โ€ข Choose an empty cell ๐Ÿกข click the โ€œMoreโ€ option represented by the three vertical dots at the end of the toolbar, as shown below.
MEDIAN Google Sheets function FAQ 1-1
โ€ข A list of icons appears when we click the โ€œMoreโ€ option. Here, click the โ€œFunctionsโ€ icon, as shown below.
MEDIAN Google Sheets function FAQ 1-2
โ€ข When we click the โ€œFunctionsโ€ icon, we get the details shown in the below image.
MEDIAN Google Sheets function FAQ 1-3
From here, we know the rest to select the required function.

2) Is there a MEDIAN IFS function in Google Sheets?

There is no MEDIAN IFS function like MEDIANIFS() in Google Sheets. However, we can build the formula using the nesting method or combining MEDIAN() and IF functions with the required multiple conditions. And then, we can execute it as an array formula using Ctrl + Shift + Enter.

3) Why is the MEDIAN formula in Google Sheets not working?

A few reasons why the Google Sheets MEDIAN function may not work are,

โ€ข The dataset contains text, blank cells, or the arguments we input are cell references to logical values.
โ€ข We combined the MEDIAN() with other functions, such as IF and IFERROR, and did not execute the expression as an array formula using Ctrl + Shift + Enter.

Download Template

This article must help understand Median Google Sheets Function with its formulas and examples. You can download the template here to use it instantly.

Guide to What Is MEDIAN Google Sheets Function. We learn how to use it to find the median of a dataset with examples and a working template. You can learn more from the following articles. โ€“

RANDBETWEEN Google Sheets Formula

Google Sheets Combo Chart

Regression Analysis In Google Sheets

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *