This tip was printed from the Tips for Spreadsheets website at http://tipsforspreadsheets.com/.

Microsoft Excel 2003 Functions and Formulas - Calculate the Median of a Group of Numbers

Although the =average function determines the average number of a series in a Microsoft Excel 2003, sometimes determining the median is helpful.

The median of a group of numbers is the middle number of a series - half of all numbers are above the median, half are below.

Calculating this number requires the =median function:

=median(SERIES)

SERIES is a series of numbers, range, or cells for which you want to find the median.

For example, assuming the following grades spreadsheet:


    A               B
1   John Smith      95
2   Sue Walker      97
3   Jim Paulson     88
4   Carol Thompson  81
5   Laura Jones     92
6
7   Average:        =AVERAGE(B1:B5)
8   Mean:           =MEDIAN(B1:B5)

The results would be:

B7: 90.6
B8: 92

Note that according to the Excel 2003 documentation, cells containing text, TRUE/FALSE, or empty cells are ignored.

Also note that if an even number of elements are in SERIES, Excel will average the sum of the two middle numbers to determine the result.

Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Calculate the Median of a Group of Numbers.

Standard disclaimer applies - Read http://tipsforspreadsheets.com/copyright-disclaim.html.