Use the Google Spreadsheets MEDIAN Function

There are a number of ways of measuring or, as it is more commonly called, the average, for a set of values.

of 05

Finding the Middle Value With the MEDIAN Function

Finding the Middle Value with Google Spreadsheets MEDIAN Function

Ted French

To make it easier to measure central tendency, Google Spreadsheets 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.
of 05

Finding the Median Mathematically

The median is most easily calculated for an odd number of values. For the numbers 2,3,4, the median, or middle value, is the number 3.

With an even number of values, the median is calculated by finding the arithmetic mean or average for the two middle values.

For example, the median for the numbers 2,3,4,5, is calculated by averaging the middle two numbers 3 and 4:

 (3 + 4) / 2

that results in a median of 3.5.

of 05

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 (number_1, number_2, ...number_30)

number_1 - (required) the data to be included in calculating the median

number_2:number_30 - (optional) additional data values to be included in the median calculations. The maximum number of entries allowed is 30

The number arguments can contain:

of 05

Example Using the MEDIAN Function

The following steps were used to enter the MEDIAN function in cell D2.

  1. Type the equal sign ( = ) followed by the name of the function median;
  2. As you type, an auto-suggest box appears with the names and syntax of functions that begin with the letter M;
  3. When the name median appears in the box, press the Enter key on the keyboard to enter the function name and an open parenthesis into cell D2;
  4. Highlight cells A2 to C2 to include them as the function's arguments;
  5. Press the Enter key to add the closing parenthesis and to complete the function;
  6. The number 6 should appear in cell A8 as the median for the three numbers;
  7. When you click on cell D2 the complete function =MEDIAN (A2C2) appears in the formula bar above the worksheet.
of 05

Blank Cells vs. Zero

When it comes to finding the median in Google Spreadsheets, 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 examples in rows four and five because a zero was added to cell B5 whereas cell B4 is blank.

 As a result​:

  •  the median for row four is calculated by adding (6 + 4)/2 = 5
  • the median for row five is just the middle of the three values: 4