If you’ve mastered the concept of median and how to manually calculate it, then using the Median function in Google Sheets will be a walk in the park. In this tutorial, we will explore everything you need to know about the Median function, from its basic usage to advanced tips and tricks. So let’s dive in!
Median – How to Manually Calculate it
Before we delve into the Median function in Google Sheets, let’s quickly go over how to manually calculate the median. The median is the middle number in a given set of numbers. To find the median, arrange the values in ascending order. The middle number in this sorted list is the median.
For example, let’s find the median of the numbers 1, 6, and 5. When we sort these values, they become 1, 5, and 6. So, the middle number is 5.
Calculating the median involves two important points:
1. Median with Odd Number of Values
In the previous example, we had three numbers, which is an odd number of values. Let’s take another example with five numbers: 1, 3, 6, 10, and 9. Once we sort these values (1, 3, 6, 9, and 10), we find that the median is 6. However, in the case of even numbers, the calculation is slightly different.
2. Median with Even Number of Values
For example, let’s find the median of the numbers 10, 5, 9, and 3. Sorting these values (3, 5, 9, and 10), we realize that we have two middle numbers, 5 and 9. To find the median, we calculate the average of these two values, which is 7.
Now that you know how to manually calculate the median, let’s explore how to use the Median function in Google Sheets.
How to Use the Median Function in Google Sheets
The Median function in Google Sheets makes calculating the median a breeze. Here’s the syntax:
=MEDIAN(value1, [value2, ...])Simply refer to the cells containing the numbers you want to calculate the median for as arguments in the Median function. The function will automatically sort the numbers and find the median, irrespective of whether you have odd or even numbers.
How to Find Conditional Median in Google Sheets
While there is no Median IF formula in Google Sheets, you can use the IF function in combination with the Median function for conditional median calculations. Let’s consider an example where we want to exclude zero values from the median calculation.
Suppose we have the values 0, 1, and 2 in the range A1:A3. To exclude the zero values, we can use either of the following formulas:
Formula 1 to Find Median Using Conditional IF
=ArrayFormula(MEDIAN(if(A1:A3>0,A1:A3)))Formula 2 to Find Median Using Filter
=median(filter(A1:A3, A1:A3>0))Both formulas achieve the same result by filtering out the zero values. I personally prefer the second formula as it utilizes the Filter function and is simpler.
Group Wise Median (Row-Wise) in Google Sheets
In Google Sheets, the Median function can be applied to groups within a sorted data range. You can sort the column you want to group, not the numbers used to find the median.
To demonstrate this, let’s consider the following scenario:
Column A is sorted, and we want to find the median of each group. The values are listed in Column B, and the medians will be calculated in Column C.
To calculate the medians in each row, copy the formula in cell C1 and paste it down. The formula will assign the median of each group to every row.
If you only want to find the median of each individual group, you can use the following formulas:
a. Formula 1:
=median(C1:C5)b. Formula 2:
=median(C6:C8)c. Formula 3:
=median(C9:C11)d. Formula 4:
=median(C12:C14)How to Calculate Median in Filtered / Hidden Rows in Google Sheets
By default, hidden or filtered rows have no effect on the median calculation in Google Sheets. However, if you want to exclude hidden values from the median calculation, follow these steps:
Let’s consider the following example:
We have a value of 30 in cell A3, which is hidden. Despite being hidden, the Median formula considers this value in the calculation.
To exclude hidden values from the median calculation, you can utilize the Subtotal function and the function number 109. I recommend using a helper column approach, as it simplifies the process.
Median Calculation Using Helper Column in a Filtered / Hidden Row Range
- Use the Subtotal function in cell C1, as shown in the image. Copy this formula and paste it down to the last row in the range (C1:C5).
- Now, use the ArrayFormula with the Median function in any other cell (e.g., cell E3) using the following formula:
=ArrayFormula(MEDIAN(if(C1:C5>0,C1:C5))) - Hide any row, and witness the magic of the Median function excluding hidden rows.
Median In Hidden Rows without Helper Column
Alternatively, you can use the virtual helper column concept to calculate the median in filtered or hidden row ranges. Here’s how:
- Enable the Show Formulae option in Google Sheets by pressing the shortcut key Ctrl+~ or going to the View menu and selecting “Show formulae.”
- Remove the
=sign from the subtotal formulas in column C, so they appear as raw formulas. - Copy the range of formulas (e.g., C1:C5) and paste it using “Paste Special” -> “Values” in the same range.
- Enclose the formulas in an open and close curly bracket to create a virtual helper column, like this:
{subtotal(109,A1);subtotal(109,A2);subtotal(109,A3);subtotal(109,A4);subtotal(109,A5)} - Now, you can use the same ArrayFormula with the Median function as before, but replace the range reference (C1:C5) with the virtual helper column:
=ArrayFormula(MEDIAN(filter({subtotal(109,A1);subtotal(109,A2);subtotal(109,A3);subtotal(109,A4);subtotal(109,A5)},{subtotal(109,A1);subtotal(109,A2);subtotal(109,A3);subtotal(109,A4);subtotal(109,A5)}>0))) - Finally, hide the values in Column C and witness the Median function excluding hidden rows.
Congratulations! You have now explored the advanced tips and tricks for using the Median function in Google Sheets. Feel free to experiment and enjoy the power of medians in your data analysis!
To learn more about Google Sheets and access additional resources, visit Crawlan.com. Happy analyzing!
