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
**G2**The 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.