While working on a dataset sometimes you need to get the middle value of the dataset. For example, if you are working on the sales chart of your company then sometimes you need the sales value after 15 days to complete the analysis. In that case, completing the task manually is not an appropriate idea. Though the median can be calculated manually if the dataset is not large yet getting the median value using a formula is a better idea. Here, we will learn how to find median in Google Sheets using different formulas and completing manually as well.
Here is the overview of this article, you will learn more once you go through the total article.
A Sample of Practice Spreadsheet
You may copy the spreadsheet below and practice by yourself.
What Is Median in Google Sheets?
Median is used to find the middlemost value or in another way, it is used to find the middle value between two numbers. For example, if the dataset contains 20 rows or 20 numbers then you can get the median value of the total dataset or you can get the median value of some selective number. You can achieve this using different types of formulas.
2 Easy Ways to Find Median in Google Sheets
The dataset below contains Employee’s Name, Team, and Weight (Kg). This dataset represents the weight of some selected employees from different teams.
1. Calculating Median Manually
Therefore, we will calculate the median using the manual process using the same dataset. Follow the steps below to do this.
- Initially, we need to sort the data given in cell range D5:D11 in ascending order and insert them as a range in F5:F11.
- So, copy the range D5:D11 and paste the values into cell F5.
- After that, select the pasted values >> go to Data >> Sort range >> Sort range by column F (A to Z) from the menu bar to sort the range.
- The output is as below.
- Here, range F5:F11 contains 7 values and the median is the middle value of a range or group. In this case, median is the 4th value as the 4th cell is the middle cell from the range.
- Moreover, if any other dataset contains even numbers of values then the median will be the sum of the middle of two values divided by two.
2. Using the MEDIAN Function
Here, we will apply the MEDIAN function with the following dataset using different methods.
2.1. Using Cell Values
In this section, we will use the MEDIAN function with cell values as inputs.
- First, select cell G4 to enter the formula.
- Then enter the formula in the same cell to execute this process.
- After that, enter the values in the formula one by one using commas to get the final result.
Thus, you will get the final median result in cell G4.
2.2. Applying Cell Ranges
Now we will apply the MEDIAN function with cell ranges using the previous formula.
- Select cell G4 and enter the MEDIAN function similarly to the previous formula.
- Complete the formula, selecting range D5:D11, and get the median of the range as below.
As a result, you will find your desired median as the output.
2.3. Combining Cell Values and Ranges
In this case, we will utilize the MEDIAN function with a combination of values and ranges.
- In the beginning, select cell G4 to execute this process.
- Consequently, enter the function in the same cell.
- Now, select the partial range D5:D9 to get the median along with the other values.
- Finally, write down the other values one by one using commas and complete the total range to get the actual median.
Consequently, your task will be accomplished successfully.
- How to Use Find and Replace in Column in Google Sheets
- Find and Replace in Google Sheets (3 Ways)
- How to Use FIND Function in Google Sheets (5 Useful Examples)
- Find and Replace with Wildcard in Google Sheets
- How to Find and Delete in Google Sheets (An Easy Guide)
How to Find Median with Condition Applied in Google Sheets
Now, we will find the median with the condition applied in google sheets using the same dataset already used in the previous methods. Say, we want to find the median for only the Production team’s employees. So, we need to apply our condition in cells C5:C11 here as the team name is specified in that column. Here, we will use the FILTER function to filter with conditions and we will find the median of the filtered values only. Follow the steps below to do this.
- First, select cell G4 to enter the formula in the cell.
- Moreover, enter the MEDIAN function to get the median from the dataset.
- Afterward, enter the FILTER function to filter the data and select the result range D5:D11 from the dataset.
- Now, enter the filter range C5:C11 to get the output from this range.
- In the end, write down the filter value and enclose the value with double quotes and complete the formula.
Things to Remember
- Median finds the center value of a dataset rather than the mean.
- Median ignores the blank cells so if the dataset is not sorted or there is a blank cell in the dataset, then this is not an issue when applying any function.
In this article, we explained how to find median in google sheets with practical examples. Hopefully, the methods will help you apply these formulas to your dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our Office Wheel blog to explore more Google Sheets-related articles.
- How to Find Slope of Trendline in Google Sheets (4 Simple Ways)
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
- How to Find Slope of Graph in Google Sheets (With Easy Steps)
- Find Linear Regression in Google Sheets (3 Methods)
- How to Find Frequency in Google Sheets (2 Easy Methods)
- Find Edit History in Google Sheets (4 Simple Ways)
- How to Find Hidden Rows in Google Sheets (2 Simple Ways)