Microsoft KB Archive/72617

{| = Calculating the Median of a Set of Values in Excel =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q72617

SUMMARY
The MEDIAN function, which represents the middle value of a set of values, is not a built-in function of Excel versions 2.1x. However, MEDIAN can be implemented as a function macro. (Note that this feature has been incorporated into Excel 3.0 as a built-in function.)

MORE INFORMATION
If there is an odd number of values in the range, the middle value is always returned by the MEDIAN function. However, there are two accepted methods to calculate the median if there is an even number of values in the range. The first returns the lesser of the two middle values. The second returns the average of the two middle values. (Excel 3.0 uses the average method to calculate the median. If the first method is desired, create a function macro as described below, making the necessary modifications for returning the lesser value.)

The following macro returns the middle value of an unsorted set of values in a vertical array. If the number of values is even, it returns the average of the two middle values. The values must be entered as a vertical array for the macro to work correctly.

A1: Median_Function A2: =ARGUMENT(&quot;RANGE&quot;,64,B:B) A3: =COUNT(RANGE) A4: =IF(MOD(A3,2)=0,SET.NAME(&quot;COUNTER&quot;,(A3/2)-1),        SET.NAME(&quot;COUNTER&quot;,(A3-1)/2)) A5: =WHILE(COUNTER>0) A6: =MAX(RANGE) A7: =MATCH(A6,RANGE,0) A8: =SET.VALUE(INDEX(RANGE,A7),MIN(RANGE)) A9: COUNTER=COUNTER-1 A10: =NEXT A11: VAR=(INDEX(RANGE,A7-1)+INDEX(RANGE,A7-2))/2 A12: =IF(MOD(A3,2)=0,RETURN(VAR)) A13: =RETURN(MAX(RANGE)) To modify this function macro to return the lesser of the two middle values when there is an even number of values in the range, remove cells A11 and A12 from the macro above and change cell A4 to read: A4: =IF(MOD(A3,2)=0,SET.NAME(&quot;COUNTER&quot;,A3/2),        SET.NAME(&quot;COUNTER&quot;,(A3-1)/2)) To modify this macro to work with a horizontal array, make the following changes:  Change the range B:B in cell A2 to 15:15. Change the INDEX portion of cell A8 to read INDEX(RANGE,,A7).  If you are using the first method of calculating the median, change the INDEX portions of cell A11 to read INDEX(RANGE,,A7-1) and INDEX(RANGE,,A7-2), respectively. 

To define this function macro, highlight cell A1 and choose Define Name from the Formula menu. Median should be in the Name: box. =$A$1 should be in the Refers To: box. Choose the Function button at the bottom to define the macro as a function macro. To use this function macro, do the following:


 * 1) With your spreadsheet active and the macro sheet open, select the spreadsheet cell into which you want to place the function.
 * 2) From the Formula menu, choose Paste Function and select the MEDIAN function, which should be at the bottom of the list. Choose OK.
 * 3) Inside of the parentheses, enter the range from which you want to calculate the median and press ENTER.

Note that when you use custom function macros in a spreadsheet, the macro sheet containing the macro must be open for the function to return a value. For more information on using the MEDIAN function in Excel 3.0, see page 150 of the &quot;Microsoft Excel Function Reference.&quot;
 * }