Excel Median Function: Find the Middle (Average) Value

01
of 01

Find the Middle Value with the MEDIAN Function

Finding the Middle Value with the MEDIAN Function in Excel
Finding the Middle Value with the MEDIAN Function. © Ted French

The Median Value as a Measure of Central Tendency in Excel

Mathematically, there are a number of ways of measuring central tendency or, as it is more commonly called, the average for a set of values. The average being the center or middle of a group of numbers in a statistical distribution.

In the case of the median,it is the middle number in a group of numbers. Half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median for the range: 2, 3, 4, 5, 6  is 4.

To make it easier to measure central tendency, Excel has a number of  functions that will calculate the more commonly used average values. These include:

  • The MEDIAN function - finds the median or middle value in a list of numbers
  • The AVERAGE function - finds the arithmetic mean for a list of numbers
  • The MODE function - finds the mode or most commonly occurring value in a list of numbers

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. How it does this is:

  • If an odd number of arguments are supplied, the function identifies the middle value in the range as the median value.
  • If an even number of arguments are supplied, the function takes the arithmetic mean or average of the middle two values as the median value.

Note: The values supplied as arguments do not need to be sorted for the function to work as shown in the example in row four above.

The MEDIAN Function's Syntax and Arguments

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

The syntax for the MEDIAN function is:

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

Number1 - (required) the data to be averaged by the function

Number2 to Number 255 - (optional) additional data values to be included in the average. The maximum number of entries allowed is 255.

This argument can contain:

Entering the Function

Options for entering the function and its arguments include:

  1. Typing the complete function such as =MEDIAN(A2:F2) into a worksheet cell
  2. Entering the function and arguments using the function's dialog box

Excel's MEDIAN Function Example

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

  1. Click on cell G2 - the location where the results will be displayed.
  2. Click on the Formulas tab
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list
  4. Click on MEDIAN in the list to bring up the function's dialog box
  5. Highlight cells A2 to F2 in the worksheet to enter the range into the dialog box
  6. Click OK to complete the function and return to the worksheet
  7. The answer 20 should appear in cell G2
  8. If you click on cell G2, the complete function =MEDIAN(A2:F2) appears in the formula bar above the worksheet

Why the Median Value is 20

For the first example in the image, since there is an odd number of arguments (5), the median value is calculated by finding the middle number - in this case 20 - since there are two numbers larger (49 and 65) and two numbers smaller (4 and 12) than it in the list

Blank Cells vs. Zero

When it comes to 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, blank cells are ignored by the MEDIAN function 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).

The Median and Displaying Zero Values in Cells

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.

To turn this option off/on:

  1. Click on the File tab of the ribbon to display the file menu options
  2. Click Options in the list to open the Excel Options dialog box, and then
  3. Click on the Advanced category in the left hand pane of the dialog box to see the available options
  4. To hide zero (0) values, In the right hand pane, in the Display options for this worksheet section clear the check box for Show a zero in cells that have zero value check box
  5. To display zero (0) values in cells ensure that the Show a zero in cells that have zero value check box is selected
Format
mla apa chicago
Your Citation
French, Ted. "Excel Median Function: Find the Middle (Average) Value." ThoughtCo, Aug. 23, 2016, thoughtco.com/excel-median-function-3123786. French, Ted. (2016, August 23). Excel Median Function: Find the Middle (Average) Value. Retrieved from https://www.thoughtco.com/excel-median-function-3123786 French, Ted. "Excel Median Function: Find the Middle (Average) Value." ThoughtCo. https://www.thoughtco.com/excel-median-function-3123786 (accessed January 19, 2018).