Hi -
Here are a couple of functions that return median.
Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose: Return median value from a recordset (uses Northwind's Orders
table as example)
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight") <enter>
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************
Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield &
">0 Order by " & pfield & ";"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)
If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close
End Function
Function Medianx(ParamArray varNums() As Variant) As Variant
'*******************************************
'Purpose: Return the median from a parameter
' array of numbers
'Coded by: raskew
'Inputs: (1) ? medianx(1,11,8,3,6,13)
' (2) ? medianx(1,11,8,3,6)
'Output: (1) 7
' (2) 6
'*******************************************
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim temp As Integer
n = UBound(varNums)
If (n < 0) Then
Exit Function
Else
'use bubble sort to sequence the elements
'(good for small number of elements but
'slow for larger sorts)
For i = 0 To UBound(varNums)
For j = 0 To UBound(varNums)
If varNums(i) < varNums(j) Then
temp = varNums(i)
varNums(i) = varNums(j)
varNums(j) = temp
End If
Next j
Next i
End If
'If there's an odd number of elements, median = center element
'e.g. if elements = 1,3,6,8,11 then median = 6
'With an even number elements, median = average of 2 center elements
'e.g. if elements = 1,3,6,8,11,13 then median = (6+8)/2 = 7
Medianx = IIf(n Mod 2 = 0, varNums(n / 2), (varNums(n \ 2) + varNums(n \
2 + 1)) / 2)
End Function
