Updated July 29, 2023
What is the Median Formula?
The median formula finds the middle value in a set of data, i.e., the middle number in a set of numbers. It tells us where the center of the data is, which can be helpful in different scenarios like studying temperatures, analyzing student scores, or finding the central age in a population.
The median formula is useful when a dataset is uneven or has extreme numbers (values that are much higher or lower than the others). In such cases, other methods, like the mean, can give a less accurate result as extreme values can influence the center value. However, the median formula is less affected by these outliers and provides a more precise estimate of the middle value.
We can calculate the median using a simple mathematical formula:
Where “n” represents the total number of items, i.e., the total number of values present in the dataset.
How to Calculate Median in Excel?
We can use the builtin function called “MEDIAN” to calculate the median in Excel.
The syntax for the MEDIAN function is as follows:
We can use the MEDIAN function in two ways:
1. Range of Dataset
Here, we must add the cell range for the data in the function as the first argument.
For example, if our data is present in the cells A2 to A 10, we will write the MEDIAN function as:
2. Array of Data
In this approach, we directly provide all the dataset values to the MEDIAN function in the form of an array. You can use this method when the dataset is small, or else directly selecting the range is an easy option.
For example, if our dataset has the following values: 2,4,6,8,10, our MEDIAN function will be:
Examples of Median Formula
Let’s take a look at some examples of median formulas. We have provided examples of both the Simple median formula as well as MEDIAN function in Excel.
We have included an Excel template with solved examples to help you better understand how to use the median function and formula in Excel.
1. Simple Median Formula Examples
Example #1: Odd Number of Observations
Calculate the Median for the dataset: A= 42, 21, 34, 65, 90, 45, 109. As the number of values in the dataset is 7, n will be 7.
Given,
Solution:
Step 1: Sort the Data in Ascending Order
It’s important to sort the dataset in ascending order before we find the median.
So, first, select the range of cells you want to sort. As per our dataset, we will select cells A5 to A11.
To sort the data from the smallest to the highest number, you have 2 options:
1. Go to the Data tab > Sort & Filter group> click on Sort.
OR
2. Rightclick over the selected data > choose Sort > Click “Sort Smallest to Largest”.
Step 2: Find the Median’s Position in the Data Set
We can calculate the median’s position using the following formula:
Median = (n + 1) / 2
= (7 + 1) / 2 = 8 / 2 = 4
Step 3: Find the Median Value
As the position of the median value in the dataset is 4. We must check what is the 4th item in the dataset. In our dataset, the 4th item is 45.
Therefore, the median value for the dataset sorted in ascending order (21, 34, 42, 45, 65, 90, 109) is 45.
Example #2: Even Number of Observations
Let us calculate the median for the given dataset: B= 2, 5, 89, 40, 66, 33, 14, 23, 90, 101. As there are 10 observations in our dataset, n will be 10.
Solution:
Step 1: Sort the Data
Sort the data in the dataset in ascending order (you can follow any of the methods shown in Example #1).
Step 2: Find the Median’s Position in the Data Set
Now, we will calculate the median’s position using the below formula,
Median = (n + 1) / 2
=(10 + 1) / 2 = 11 / 2
Median = 5.5
Step 3: Find the Median
Since the position of the median is 5.5, it means the median value is the average of the values present at the 5th and 6th positions of the dataset.
As per our dataset arranged in ascending order (2, 5, 14, 23, 33, 40, 66, 89, 90, 101), the 5th and 6th values are 33 and 40, respectively.
To find the average of 33 and 40, we must add both numbers and divide the result by 2.
Median = (33 + 40)/2 = 73/2 = 36.5
Thus, the median value for the dataset is 36.5.
2. MEDIAN Function in Excel Examples
Example #1: Dataset with Odd Number of Values
A company has the following sales data for the years 2014 to 2022.
Year  2014  2015  2016  2017  2018  2019  2020  2021  2022 
Sales  141  177  157  191  211  206  214  202  212 
Let us find the median for the above data using the MEDIAN function.
Given,
Solution:
While using the MEDIAN function, there is no need to sort the data in ascending order. The function does this automatically before finding the median.
Step 1: To find the median, add the following formula in the cell B12:
Step 2: Press “Enter”. The MEDIAN function finds and returns the median value.
Here, the function returns 202 as the median value for the given range (B2:B10).
So, the median for the given sales data is 202.
Example #2: Dataset with an Even Number of Values
Let us find the median value for the following data using the MEDIAN function.
C = 13, 25, 12, 40, 27, 80, 55, 62, 99,73
Solution:
Step 1: Enter the folding formula in the cell B13:
Step 2: Press “Enter”. The MEDIAN function will calculate and return the middle value.
Here, the function returns the value of 47.50. It is the average between the two middle values of the dataset (40 and 55).
Basically, the function found the two middle values, 40 and 55, and found their average: (40 + 55) /2 = 47.5.
Therefore, the median for the dataset (13, 25, 12, 40, 27, 80, 55, 62, 99,73) is 47.5.
Things to Keep in Mind
1. Use the COUNTA function to find the value for ‘n’:
If you want to find the number of values in a large dataset containing hundreds or thousands of values, you can use the COUNTA function to find the ‘n’ value.
2. The MEDIAN function automatically neglects the empty cells and text:
While calculating the median, the MEDIAN function will automatically ignore any cell with a nonnumeric value, i.e., if the cell has text or is empty.
3. The MEDIAN function counts the logical values:
The MEDIAN function takes into account logical values (TRUE and FALSE) during the calculation. TRUE is treated as 1, while FALSE is treated as 0.
Importance and Uses of Median Formula
 The Median is the point where 50% of the numbers are above & 50% of the numbers are below. It is a centrality denoting the middle value, which is helpful for a historical dataset or data set over time.
 It is advantageous when the data set includes very high and low values or the data in the group is mixed (texts, empty values, numbers, etc.).
 Economists use median values to draw conclusions and frame economic policies. For instance, they can determine the median price of houses in a specific area to see if houses in that area are affordable or expensive.
Median Formula Calculator
Use the following medium calculator for calculating the median.
n  
Median Formula  
Median Formula = 


Frequently Asked Questions(FAQs)
Q1. What is n in the median formula?
Answer: The variable “n” in the median formula represents the total number of values in the dataset. For example, if you have the following dataset of 7 values:
3, 5, 1, 4, 6, 8, 2
The value of “n” would be 7 because there are 7 values in the dataset.
Q2. What is the relationship between mean, mode, and median?
Answer: In statistics, the mean, median, and mode have a connection known as the empirical relationship. It means that if we know the value of any of the two metrics, we can find the value of the remaining one. We can use the following equation to find this:
For example, if the median value is 4 and the mean value is 3, we can find the mode value.
Mode = (3 x 4) – (2 x 3) = 12 – 6
Mode = 6
Q3. What are the two formulas of median?
Answer: We have two formulas for the median, i.e., the odd number of observations and the even number of observations.
 If the number of terms(n) is even, then the formula is ½ [(n/2)+((n/2)+1)]
 If the number of terms(n) is odd, then the formula is (n+1)/2
Q4. What is the conditional median formula?
Answer: If you want to find the median for a group of data based on a condition, you can use the conditional median formula. For instance, if you want to find the median for all values that are below 100, we can use the conditional median formula. Here we use the IF function combined with the MEDIAN function. The IF function checks if a given condition is true for each value in the dataset. If the condition is true, the MEDIAN function includes that value when determining the median of the dataset.
The syntax is as follows:
Recommended Articles
This article shows how to calculate the median in Excel using the median formula and the median Excel function. We have explained each formula and how to use them and given solved examples with a downloadable Excel template. You can also use the calculator to directly find the median value for a dataset. You may also look at the following articles to learn more,