# How calculate median

I have a report that shows the record ID and a number field in the detail section.  I can get the average with the sum of the number field divided by the record count in the report footer.

But how can I calculate the median average?
###### Who is Participating?

Remote Training and ProgrammingCommented:
This code averages the 2 middle values if there is not an odd number of values -- you may want to change this. Send it the SQL statement you want it to use where the first field is what you want the median of.
``````Function GetMedian(sSQL As String) As Double
' 121103 Crystal
' strive4peace

On Error GoTo Proc_Err

Dim db As DAO.Database _
, rs As DAO.Recordset

Dim nDbl As Double _
, nNumValues As Long

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rs.EOF Then
With rs
.MoveLast
.MoveFirst
nNumValues = .RecordCount
Select Case True

Case nNumValues = 1
GetMedian = Nz(.Fields(0).Value, 0)

Case (nNumValues / 2 - nNumValues \ 2) < 0.001 'even
'average 2 middle values
.Move ((nNumValues \ 2) - 1)
nDbl = Nz(.Fields(0).Value, 0)
.MoveNext
GetMedian = (nDbl + Nz(.Fields(0).Value, 0)) / 2

Case Else 'odd
.Move (nNumValues \ 2)
GetMedian = Nz(.Fields(0).Value, 0)

End Select
End With 'rs
End If

Proc_Exit:
On Error Resume Next
'release object variables
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& "   GetMedian"

Resume Proc_Exit
Resume

End Function
``````
0

Author Commented:
Must be doing something wrong or not complete.  I copy/pasted your code in the bottom of the reports VBA editor.  Now how do I make the textbox populate with the median on the report?
0

Remote Training and ProgrammingCommented:
``````=GetMedian("SELECT MyField FROM MyTable WHERE MyConditions")
``````

substitute :
MyField with the fieldname
MyTable with the table or query name
MyConditions with the criteria
0

Author Commented:
Thank you again.  Much appreciated.
0

Remote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.