Calculating the Median of a Set of Values in Excel

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("RANGE",64,B:B)
   A3:  =COUNT(RANGE)
   A4:  =IF(MOD(A3,2)=0,SET.NAME("COUNTER",(A3/2)-1),
        SET.NAME("COUNTER",(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("COUNTER",A3/2),
        SET.NAME("COUNTER",(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:

With your spreadsheet active and the macro sheet open, select the spreadsheet cell into which you want to place the function.
From the Formula menu, choose Paste Function and select the MEDIAN function, which should be at the bottom of the list. Choose OK.
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 "Microsoft Excel Function Reference."

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.