Microsoft KB Archive/64339

{| = Excel: Finding the Median of a Sorted Series of Numbers =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q64339

SUMMARY
Note: The following article pertains only to versions of Microsoft Excel previous to version 3.00. Excel 3.00 introduced the MEDIAN function. For more information on MEDIAN, see page 150 in the &quot;Microsoft Excel Function Reference&quot; version 3.0 manual.

To compute the median of a sorted series of numbers in Excel, use the following formula

=IF(MOD(COUNT(range),2)<>0,INDEX(range,INT(COUNT(range)/2)+1,1),  (INDEX(range,COUNT(range)/2,1)+INDEX(range,(COUNT(range)/2)+1,1))/2) where &quot;range&quot; is the array of cells that contain the series of numbers. For example, if the series of numbers were located in cells A1 through A10, then A1:A10 would be entered as the range in the above formula. Note: The series of numbers must be sorted for this formula to work.

The median of a series of numbers is defined to be the middle number in a series with an odd number of items, or the average of the two middle numbers in a series with an even number of items. For example, consider the following series of numbers:

1, 4, 7, 16, 43 In this case, the median is 7 because there is an odd number of items in the series, and 7 is the middle number. In the following example

3, 4, 8, 12, 46, 72 the median is 10 because there are an even number of items in the series. As a result, the average of the two middle numbers (8 and 12) is computed, giving a result of 10.

MORE INFORMATION
This function can also be entered into a function macro, as follows:

  Open a new macro sheet and type the following: Cell  Entry -

A1    Median A2    =ARGUMENT(&quot;range&quot;,64) A3    =RETURN(IF(MOD(COUNT(range),2)<>0,INDEX(range,              INT(COUNT(range)/2)+1, 1), (INDEX(range,COUNT(range)/2,1)              +INDEX(range,(COUNT(range)/2)+1,1))/2))  Highlight cell A1 and choose Define Name from the Formula menu. &quot;Median&quot; should appear in the Name box and &quot;=$A$1&quot; should appear in the Refers To box. Click the Function radio button to define the macro as a function macro, and click OK.

To use this function macro, do the following:
 * 1) With your spreadsheet active and the macro sheet open, select the spreadsheet cell in which you want to place the function. From the Formula menu, choose Paste Function and select the Median function, which should appear at the bottom of the list with the syntax &quot;Macroname!Median&quot;. Click OK.
 * 2) Inside the parentheses, enter the range that you want the median to be and press RETURN.
 * }