Excel: Finding the Median of a Sorted Series of Numbers

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 "Microsoft Excel Function Reference" 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)

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 the following example

   3, 4, 8, 12, 46, 72

MORE INFORMATION

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

1. Open a new macro sheet and type the following:

         Cell   Entry
         ----   -----
   
         A1     Median
         A2     =ARGUMENT("range",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))
   
   

2. Highlight cell A1 and choose Define Name from the Formula menu. "Median" should appear in the Name box and "=$A$1" should appear in the Refers To box.

3. Click the Function radio button to define the macro as a function macro, and click OK.

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 "Macroname!Median". Click OK.

2. Inside the parentheses, enter the range that you want the median to be and press RETURN.