Microsoft Excel has several functions that calculate commonly used averages. The MEDIAN function finds the median or average value in a list of numbers.
These instructions apply to Excel 2019, 2016, 2013, and 2010, as well as Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online.
The MEDIAN function sorts the provided arguments to find the value that is arithmetically in the middle of the group.
With an odd number of arguments, the MEDIAN function identifies the mean of the range as the median.
If the number of arguments is even, the function takes the median as the arithmetic mean, or the average of the two middle values.
The values provided as arguments do not need to be in any particular order for the function to work.
MEDIAN function syntax
The function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.
Here is the syntax of the MEDIAN function:
=MEDIAN(number1, number2, number3, …)
=MEDIUM is the starting point for all MEDIAN formulas. number 1 refers to the required data that the function calculates. number 2 and the following values refer to optional additional data values calculated on average. The maximum number of entries allowed is 255, each separated by a comma.
This argument can contain a list of numbers to average, cell references to the location of the data in the worksheet, a range of cell references, and a named range.
Enter the full function in a cell on the worksheet, or use the Function dialog box to enter the function and arguments.
Example of a MEDIAN function
These steps describe how to enter the MEDIAN function and arguments using the dialog box. We will use sample data entered into a spreadsheet as shown below.
- Select a cell G2The results are displayed there.
- Choose insert function to open the insert function dialog box.
- Choose statistics in which category Listing.
- Choose MEDIAN from the list of features, and then select OK.
- highlight cells A2 to F2 in the worksheet to automatically insert this range.
- To press Enter or To return to complete the function and return to the worksheet.
- For our sample data, the answer 20 should appear in cell G2.
If you click on cell G2, the full function will appear. =MEDIAN(A2 : F2)appears in the formula bar above the worksheet.
Why is the median 20? Because this row of data has an odd number of arguments (five), the median is calculated by finding the middle number. It’s 20 here because there are two larger numbers (49 and 65) and two smaller numbers (4 and 12).
Empty cells versus null values
When searching for the median in Excel, there is a difference between empty cells and those containing a null value. The MEDIAN function ignores empty cells, but not those containing a null value.
By default, Excel displays a zero in cells with a zero value. This option can be turned off so that when the median is calculated, the zero value of that cell is always included as a function argument if cells are left blank.
To enable and disable this option:
In Excel 2019, Excel 2016, Excel 2013 and Excel 2010
- Step aside dossier and select options.
- Go to the Progressive in the left pane of options.
- Scroll down on the right side until you find it Display options for this worksheet Section.
- To hide zero values in cells, delete the Displays a zero in cells that have a null value check box. To show zeros, select the check box.
- Save changes with OK Button.
In Excel 2019 for Mac, Excel 2016 for Mac, and Excel for Mac 2011
- Step aside Excel Menu.
- Choose settings.
- sous author Choose See.
- Delete those Show null values Check the box below window options.
This option cannot be turned off in Excel Online.