Excel has several functions that will calculate commonly used average values. The MEDIAN function finds the *median* or middle value in a list of numbers.

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 through the provided arguments to find the value that falls arithmetically in the middle of the group.

If there is an odd number of arguments, the function identifies the middle value in the range as the median value.

If there is an even number of arguments, the function takes the arithmetic mean or the average of the middle two values as the median value.

### Arguments

The values supplied as arguments do not need to be in any particular order for the function to work. You can see that in play in the fourth row in the example image below.

## MEDIAN Function Syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The following is the syntax for the MEDIAN function:

=MEDIAN(Number1, Number2, Number3, ...)

**=MEDIAN**: All MEDIAN formulas begin this way.**Number1***:*Required data to be calculated by the function.**Number2***:*Optional additional data values to be calculated in the average. The maximum number of entries allowed is 255, each of which needs to be separated by a comma.

This argument can contain:

- A list of numbers to be averaged
- Cell references to the location of the data in the worksheet
- A range of cell references
- A named range

Options for entering the function and its arguments:

- Typing the complete function, like
**=MEDIAN(A2: F2)**, into a worksheet cell - Entering the function and arguments using the function's dialog box

## MEDIAN Function Example

These steps detail how to enter the MEDIAN function and arguments using the dialog box for the first example displayed in the above image.

- Click on cell
**G2**, which is where the results will display. - Click the
**Insert Function**button to open the Insert Function dialog box. - Choose
**Statistical**in the Category list. - Select
**MEDIAN**in the list of functions and click**OK**. - Highlight cells
**A2 to F2**in the worksheet to automatically insert that range. - Press
**Enter**to complete the function and return to the worksheet.

The answer **20 **should appear in cell G2

If you click on cell G2, the complete function, **=MEDIAN(A2: F2)**, appears in the formula bar above the worksheet.

Why is the median value 20? For the first example in the image, since there's an odd number of arguments (five), the median value is calculated by finding the middle number. It's 20 here because there are two numbers greater (49 and 65) and two numbers smaller (4 and 12).

## Blank Cells vs. Zero Values

When finding the median in Excel, there is a difference between blank or empty cells and those containing a zero value.

As shown in the examples above, the MEDIAN function ignores blank cells but not those containing a zero value.

- The median changes between the first and second examples because a zero was added to cell A3 whereas cell A2 is blank.
- The addition of 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 middle values (12 and 20) together and then dividing by two to find their average (16).

By default, Excel displays a zero (0) in cells with a zero value, as shown in the example above. This option can be turned off and, if done, such cells are left blank, but the zero value for that cell is still included as an argument for the function when calculating the median.

This option cannot be turned off in Excel Online.

How to toggle this option on and off in** Excel 2019, Excel 2016, Excel 2013, and Excel 2010**:

- Go to the
**File**tab and click**Options**. - Go into the
**Advanced**category from the left pane of the options. - On the right side, scroll down until you find the
**Display Options for This Worksheet**section. - To hide zero values in cells, clear the
**Show a zero in cells that have zero value**checkbox. To display zeros, put a check in the box. - Save any changes with the
**OK**button.

How to toggle this option on and off in **Excel 2019 for Mac, Excel 2016 for Mac, and Excel for Mac 2011**:

- Go to the
**Excel**menu. - Click
**Preferences**. - Click
**View**under Authoring. - Clear the
**Show Zero Values**checkbox under**Window Options**.