Calculate median in Excel: with two practical examples
The median is a form of measure of central tendency that represents the middle value in a sorted list of numbers. In Excel, finding the median is straightforward, thanks to the built-in MEDIAN function. Unlike the average, the median is not skewed by extremely high or low values, making it a robust indicator of the data's center.
This function sorts a range of numbers (number1, number2,…) and returns the one that falls in the middle. For an odd number of observations, the median is the number that splits the dataset into two halves. For an even number of observations, Excel averages the two middle numbers.
The MEDIAN function in Excel excludes text, logical values, and blank cells but includes zeroes in its calculation.
How to calculate median in Excel with MEDIAN function – two formula examples
The two scenarios outlined below represent the most frequently encountered cases when it comes to calculating the median in Excel.
Example 1: Find the median from a range of cell values
For getting the median in range A2:A7, please use below formula in a blank cell and then press Enter key, the median will be displayed.
Note: If you want to find the median from several discontinuous range, let’s say from A2:A3, A5:A7, please use the formula like this:
A Grand Array of Formulas for Complex Calculations - Clicks Away, No Memorization Required
Beyond the MEDIAN function, Excel's AVERAGE function typically finds the dataset's central value. However, it falters when calculating the average of only visible cells. Luckily, Kutools for Excel offers a quick-fix formula -- AVERAGEVISIBLE, that resolves this with just two clicks. Furthermore, it amasses a comprehensive suite of functions and formulas to conquer the myriad of complex calculations encountered in everyday work. Download now and unlock the full potential of Kutools for Excel.
Example 2: Find the median from a range of cell values excluding zeros
Typically, Excel's MEDIAN function considers zeroes in its calculations. However, if you need to find the median while omitting zero values, you can do so by integrating the IF function with the MEDIAN function.
To calculate the median while excluding zeros in the range A2:A7, enter the following formula in a blank cell and press the Enter key to display the median.
Explanation of the formula::
IF(A2:A7>0, A2:A7): This part of the formula creates an array based on the range A2:A7. For each cell in that range, it checks whether the cell's value is greater than zero. If it is, the value of the cell is included in the array; if not (meaning if the value is zero or negative), it is excluded from the array that will be considered for the median calculation.
MEDIAN(IF(A2:A7>0,A2:A7)): The MEDIAN function then takes this array (with zeros excluded) and calculates the median value.
The insights shared above examples to calculate median in Excel. I trust this information serves you well. For more game-changing Excel strategies that can elevate your data management, explore further here..