Microsoft Office Development and Consultancy
 Home|

Excel

|VBA|C#|Finance|Tools|Newsletter|Feedback|Contact 
 Excel > Functions > Statistical > MEDIAN
 

MEDIAN(number1, number2 [,number3] [,...])

 
 Returns the median of the numbers in a list or cell range.

 number1The first number.
 number2The second number.
 number3(Optional) The third number.

 REMARKS
 
  		• The median is the middle of the distribution after the numbers have been sorted into ascending order.
     
  		• If there is an odd number of unique numbers in the set, then the number in the middle is returned. See Example 1.
     
  		• If there is an even number of unique numbers in the set, then the average of the two numbers in the middle is calculated. See Example 2.
     
  		• The arguments can be values, cell references, arrays or named ranges.
     
  		• If any arguments evaluate to zero, they are included.
     
  		• If any arguments evaluate to True or False, then they are ignored.
     
  		• If any arguments contain text, then #VALUE! is returned. Example 21.
     
  		• If any cell references contain text, then they are ignored. See Example 13.
     
  		• If any cell references are empty, then they are ignored.
     
  		• You can have a maximum of 30 arguments.
     
  		• Example 9 - Using curly brackets to define an array of items is optional and is not related to pressing (Ctrl + Shift + Enter).
     
  		• Example 10 & 11 - Referencing a single vertical column will give the correct median value.
     
  		• Example 12 & 13 - Referencing a single horizontal row will also give the correct median value.
     
  		• Example 14 & 15 - Referencing more than a single row or column will also give the correct median value.
     
  		• Example 16 & 17 - Array Formulas. Referencing more than a single row or column will also give the correct median value. Entering these formulas using (Ctrl + Shift + Enter) is not necessary as the result is the same either way.
     
  		• Example 18 - Any zero values will be included. Compare this with Example 2.
     
  		• MEDIANIF - Returns the median value in a range of values that match a certain condition.

     EXAMPLES
     
     ABCD
    1=MEDIAN(1,2,3) = 243 
    2=MEDIAN(1,2,3,4) = 2.521 
    3=MEDIAN(4,3,2,1) = 2.556 
    4=MEDIAN(B1,C1,B2,C2) = 2.550040 
    5=MEDIAN(1,2,3,4,5) = 330600text
    6=MEDIAN(2,10) = 6   
    7=MEDIAN(1,4,5) = 4   
    8=MEDIAN(1,4,6,5) = 4.5   
    9=MEDIAN({1,4,6,5}) = 4.5   
    10=MEDIAN(B1:B4) = 4.5   
    11=MEDIAN(B1:B5) = £5.00   
    12=MEDIAN(B5:C5) = £315.00   
    13=MEDIAN(B5:D5) = £315.00   
    14=MEDIAN(B1:C2) = 2.5   
    15=MEDIAN(B1:D5) = 6   
    16={MEDIAN(B1:C2)} = 2.5   
    17={MEDIAN(B1:D5)} = 6   
    18=MEDIAN(1,2,3,4,0) = 2   
    19=MEDIAN({1,4,6,5,"some text"}) = 4.5   
    20=MEDIAN({1,4,6,5,"some text",TRUE}) = 4.5   
    21=MEDIAN(1,4,6,5,"some text",TRUE) = #VALUE!   
    		 
     Functions - M | Index - M | Office Online 2013 | 2010 | 2007 | 2003 

     © 2013 Better Solutions Limited. All Rights Reserved.Top | 03-Jan-2013