- Using the MEDIAN Function in Microsoft Excel
- How the MEDIAN function works
- MEDIAN Function Syntax
- MEDIAN function example
- Empty cells vs null values
Using the MEDIAN Function in Microsoft Excel
Excel has several functions that calculate commonly used averages. The MEDIAN function finds median or the middle value in a list of numbers.
Remark † These instructions apply to Excel 2019, 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Office 365, and Excel Online.
How the MEDIAN function works
The MEDIAN function sorts the given arguments to find the value that falls arithmetically in the middle of the group.
If there are an odd number of arguments, the function identifies the middle value in the range as the middle value.
If there are an even number of arguments, the function takes the arithmetic mean, or the average of two means.
The values supplied as arguments do not have to be in any particular order for the function to work. You can see it in the game on the fourth row in the sample image below.
MEDIAN Function Syntax
Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.
Following is the syntax for the MEDIAN function:
= MEDIAN ( Number1 , Number2 , Number3 , ... )
- = MEDIAN † All MEDIAN formulas start like this.
- Number 1 † Required data to be calculated by the function.
- Number 2 † Optional additional data values to calculate the mean. The maximum number of entries allowed is 255, each of which must be separated by a comma.
This argument can contain:
List of numbers to average
Cells refer to the location of the data on the sheet
Cell reference range
Options for entering the function and its arguments:
Enter a complete function, for example =MEDIAN(A2:F2) to a leaf cell
Entering a function and arguments using the Function dialog box
MEDIAN function example
These steps describe how to enter the MEDIAN function and arguments using the dialog box for the first example shown in the image above.
Click on a cell G2 where the results are displayed.
Press the button Insert function to open the Insert Function dialog box.
Select Statistical in the category list.
Select MEDIAN in the feature list and click Okay †
Highlight cells A2 to F2 on the sheet to automatically insert that range.
Click Enter to end the function and return to the worksheet.
Reply twenty should appear in cell G2
If you click on cell G2, the full function =MEDIAN(A2:F2) appears in the formula bar above the worksheet.
Why median 20? For the first example in the figure, since there is an odd number of arguments (five), the mean is calculated by finding the mean. It is 20 because there are two numbers greater (49 and 65) and two numbers less (4 and 12).
Empty cells vs null values
When finding the median in Excel, there is a difference between blank or empty cells and cells with a null value.
As shown in the above examples, the MEDIAN function ignores empty cells, but not cells that contain a null value.
The median changes between the first and second example because a zero has been added to cell A3 and cell A2 is empty.
Adding a zero to cell A3 changes the number of arguments passed to the function in cell G3 from five to six, an even number. As a result, the median is calculated by adding the two means (12 and 20) together and then dividing by two to find their mean (16).
By default, Excel displays zero (0) in cells with a zero value, as shown in the example above. This option can be disabled, and if done, such cells will remain blank, but the zero value for that cell will still be included as a function argument when calculating the median.
Remark † This option cannot be disabled in Excel Online.
How to enable or disable this setting in Excel 2019, Excel 2016, Excel 2013 and Excel 2010 †
Go to the tab File and press parameters: †
Go to category Additionally in the left options panel.
Scroll down on the right until you find the section Display options for this worksheet †
Uncheck to hide null values in cells Show zero in cells with null value † Check the box to show zeros.
Save any changes with the button Okay †
How to enable or disable this setting in Excel 2019 for Mac, Excel 2016 for Mac, and Excel for Mac 2011 †
Go to menu excel †
Click Institutions †
Click Vision in the “Authorization” section.
Uncheck the box Show null values in section Window options †