Search code examples
excelvba

Calculate median using vba

How do I calculate the median for a range of more than 30 values using VBA? The WorkSheetFormula method can handle only 30 values. My function at the moment looks like this;

Function medval(Longitudes As Range)
'I want the median for values in the range (here 'Longitudes') B2:B100)
End Function
Solution

  • You can average/find median of as many contiguous data values as you want:

    =MEDIAN(A1:A65500) will work

    but you cannot exceed 30 individual references in Excel 2003 (in 2007 you can have 255 references).

    i.e.

    =MEDIAN(A1,A2,A3,...A31) will not work.

    A range of values will work as long as they are contiguous