How do I calculate the median/other stats

[Guest]

I am very new to Access and would like to calculate the median of various
groupings of data - I have no trouble grouping by average, min, max etc. but
I don't know how to calculate the median. Any help would be much appreciated.

TH

 

[Jeff Boyce]

One approach would be to "roll your own" function to do this calculation
(Access doesn't have a "native" function for this).

Another approach would be to set a reference to Excel and 'borrow' Excel's
MEDIAN function.

Or you could locate a statistical package (FMS, Inc. has one for sale)
designed to be used in Access.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[raskew via AccessMonster.com]

Hi -

Here's a UDF you might try:

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

HTH - Bob

 

[Guest]

Here is a UDF that I've used for a while. Works similiar to DMAX in that it
requires a fieldname, tablename, and accepts an optional criteria. It will
return a NULL if no records match the criteria.

Public Function fnMedian(ByVal Fieldname As String, ByVal Tablename As
String, _
Optional ByVal Criteria As String) As Variant

Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset
Dim intRecCount As Integer, intPointer As Integer

strSQL = "SELECT [" & Fieldname & "] FROM [" & Tablename & "] "
strCriteria = " WHERE [" & Fieldname & "] IS NOT NULL"
If Len(Criteria) > 0 Then strCriteria = strCriteria & " AND " & Criteria
strSQL = strSQL & strCriteria & " ORDER BY [" & Fieldname & "]"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'If no records in the recordset, return a Null value
If rs.EOF Then
fnMedian = Null
GoTo fnMedianExit
End If

rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst

'Move to the center record(s)
For intPointer = 2 To (intRecCount + 1) \ 2
rs.MoveNext
Next intPointer

fnMedian = Val(rs(0))
If intRecCount Mod 2 = 0 Then 'average center two values
rs.MoveNext
fnMedian = Val(fnMedian) + Val(rs(0))
fnMedian = fnMedian / 2
End If

fnMedianExit:

If Not rs Is Null Then
rs.Close
Set rs = Nothing
End If

Exit Function

fnMedianError:

MsgBox Err.Number, Err.Description
GoTo fnMedianExit

End Function

HTH
Dale

 

[Guest]

Oops!

Replace: If Not rs Is Null Then
with: If Not rs Is Nothing then

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Here is a UDF that I've used for a while. Works similiar to DMAX in that it
requires a fieldname, tablename, and accepts an optional criteria. It will
return a NULL if no records match the criteria.

Public Function fnMedian(ByVal Fieldname As String, ByVal Tablename As
String, _
Optional ByVal Criteria As String) As Variant

Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset
Dim intRecCount As Integer, intPointer As Integer

strSQL = "SELECT [" & Fieldname & "] FROM [" & Tablename & "] "
strCriteria = " WHERE [" & Fieldname & "] IS NOT NULL"
If Len(Criteria) > 0 Then strCriteria = strCriteria & " AND " & Criteria
strSQL = strSQL & strCriteria & " ORDER BY [" & Fieldname & "]"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'If no records in the recordset, return a Null value
If rs.EOF Then
fnMedian = Null
GoTo fnMedianExit
End If

rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst

'Move to the center record(s)
For intPointer = 2 To (intRecCount + 1) \ 2
rs.MoveNext
Next intPointer

fnMedian = Val(rs(0))
If intRecCount Mod 2 = 0 Then 'average center two values
rs.MoveNext
fnMedian = Val(fnMedian) + Val(rs(0))
fnMedian = fnMedian / 2
End If

fnMedianExit:

If Not rs Is Null Then
rs.Close
Set rs = Nothing
End If

Exit Function

fnMedianError:

MsgBox Err.Number, Err.Description
GoTo fnMedianExit

End Function

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I am very new to Access and would like to calculate the median of various
groupings of data - I have no trouble grouping by average, min, max etc. but
I don't know how to calculate the median. Any help would be much appreciated.

TH

 

[Smartin]

Oops!

Replace: If Not rs Is Null Then
with: If Not rs Is Nothing then

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Here is a UDF that I've used for a while. Works similiar to DMAX in that it
requires a fieldname, tablename, and accepts an optional criteria. It will
return a NULL if no records match the criteria.

Public Function fnMedian(ByVal Fieldname As String, ByVal Tablename As
String, _
Optional ByVal Criteria As String) As Variant

Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset
Dim intRecCount As Integer, intPointer As Integer

strSQL = "SELECT [" & Fieldname & "] FROM [" & Tablename & "] "
strCriteria = " WHERE [" & Fieldname & "] IS NOT NULL"
If Len(Criteria) > 0 Then strCriteria = strCriteria & " AND " & Criteria
strSQL = strSQL & strCriteria & " ORDER BY [" & Fieldname & "]"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'If no records in the recordset, return a Null value
If rs.EOF Then
fnMedian = Null
GoTo fnMedianExit
End If

rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst

'Move to the center record(s)
For intPointer = 2 To (intRecCount + 1) \ 2
rs.MoveNext
Next intPointer

fnMedian = Val(rs(0))
If intRecCount Mod 2 = 0 Then 'average center two values
rs.MoveNext
fnMedian = Val(fnMedian) + Val(rs(0))
fnMedian = fnMedian / 2
End If

If Not rs Is Null Then
rs.Close
Set rs = Nothing
End If

Exit Function

MsgBox Err.Number, Err.Description
GoTo fnMedianExit

End Function

HTH
Dale

Email address is not valid.
Please reply to newsgroup only.

"TH" wrote:

Here's a pure SQL approach that might work for you. This does not
calculate a proper mathematical median, but returns an actual value
from the middle of your ordered table. (It's virtually the same thing,
especially if the number of rows in the analysis is large).

This assumes you want the median RptPayLag based on specified values
in categories [Accident Year] and [Amount Code].

Note: after you save the SQL Access/JET will do wonky things with the
syntax. Save an original version of your SQL off line in a text file
just in case.

SELECT TOP 1 [Accident Year], [Amount Code], RptPayLag
FROM (SELECT * FROM
(SELECT TOP 50 PERCENT *
FROM PLDATA
WHERE [ACCIDENT YEAR] = [AY?] AND
[AMOUNT CODE] = [AMT CODE?]
ORDER BY RPTPAYLAG ASC)
ORDER BY RPTPAYLAG DESC);

 

[Michel Walsh]

If you have an even number of elements, the median is the mean of the two
central ones. Your solution returns the central-left value. In other words,
with the values of { 1, 3 } , the median is 2, but the solution you
proposed returns 1.

You can use MAX rather than the outmost TOP 1 (finding the MAX should be
faster than re-ordering, but I haven't tested it).


Vanderghast, Access MVP

Oops!

Replace: If Not rs Is Null Then
with: If Not rs Is Nothing then

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Here is a UDF that I've used for a while. Works similiar to DMAX in
that it
requires a fieldname, tablename, and accepts an optional criteria. It
will
return a NULL if no records match the criteria.

Public Function fnMedian(ByVal Fieldname As String, ByVal Tablename As
String, _
Optional ByVal Criteria As String) As Variant

Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset
Dim intRecCount As Integer, intPointer As Integer

strSQL = "SELECT [" & Fieldname & "] FROM [" & Tablename & "] "
strCriteria = " WHERE [" & Fieldname & "] IS NOT NULL"
If Len(Criteria) > 0 Then strCriteria = strCriteria & " AND " &
Criteria
strSQL = strSQL & strCriteria & " ORDER BY [" & Fieldname & "]"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

'If no records in the recordset, return a Null value
If rs.EOF Then
fnMedian = Null
GoTo fnMedianExit
End If

rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst

'Move to the center record(s)
For intPointer = 2 To (intRecCount + 1) \ 2
rs.MoveNext
Next intPointer

fnMedian = Val(rs(0))
If intRecCount Mod 2 = 0 Then 'average center two values
rs.MoveNext
fnMedian = Val(fnMedian) + Val(rs(0))
fnMedian = fnMedian / 2
End If

If Not rs Is Null Then
rs.Close
Set rs = Nothing
End If

Exit Function

MsgBox Err.Number, Err.Description
GoTo fnMedianExit

End Function

HTH
Dale

Email address is not valid.
Please reply to newsgroup only.

"TH" wrote:

I am very new to Access and would like to calculate the median of
various
groupings of data - I have no trouble grouping by average, min, max
etc. but
I don't know how to calculate the median. Any help would be much
appreciated.

TH

Here's a pure SQL approach that might work for you. This does not
calculate a proper mathematical median, but returns an actual value
from the middle of your ordered table. (It's virtually the same thing,
especially if the number of rows in the analysis is large).

This assumes you want the median RptPayLag based on specified values
in categories [Accident Year] and [Amount Code].

Note: after you save the SQL Access/JET will do wonky things with the
syntax. Save an original version of your SQL off line in a text file
just in case.

SELECT TOP 1 [Accident Year], [Amount Code], RptPayLag
FROM (SELECT * FROM
(SELECT TOP 50 PERCENT *
FROM PLDATA
WHERE [ACCIDENT YEAR] = [AY?] AND
[AMOUNT CODE] = [AMT CODE?]
ORDER BY RPTPAYLAG ASC)
ORDER BY RPTPAYLAG DESC);

 

[Smartin]

If you have an even number of elements, the median is the mean of the two
central ones. Your solution returns the central-left value. In other words,
with the values of { 1, 3 } , the median is 2, but the solution you
proposed returns 1.

You can use MAX rather than the outmost TOP 1 (finding the MAX should be
faster than re-ordering, but I haven't tested it).

Vanderghast, Access MVP

Oops!
Replace: If Not rs Is Null Then
with: If Not rs Is Nothing then
Dale
--
Don''t forget to rate the post if it was helpful!
Email address is not valid.
Please reply to newsgroup only.
:
Here is a UDF that I've used for a while. Works similiar to DMAX in
that it
requires a fieldname, tablename, and accepts an optional criteria. It
will
return a NULL if no records match the criteria.
Public Function fnMedian(ByVal Fieldname As String, ByVal Tablename As
String, _
Optional ByVal Criteria As String) As Variant
Dim strSQL As String, strCriteria As String
Dim rs As DAO.Recordset
Dim intRecCount As Integer, intPointer As Integer
strSQL = "SELECT [" & Fieldname & "] FROM [" & Tablename & "] "
strCriteria = " WHERE [" & Fieldname & "] IS NOT NULL"
If Len(Criteria) > 0 Then strCriteria = strCriteria & " AND " &
Criteria
strSQL = strSQL & strCriteria & " ORDER BY [" & Fieldname & "]"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
'If no records in the recordset, return a Null value
If rs.EOF Then
fnMedian = Null
GoTo fnMedianExit
End If
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
'Move to the center record(s)
For intPointer = 2 To (intRecCount + 1) \ 2
rs.MoveNext
Next intPointer
fnMedian = Val(rs(0))
If intRecCount Mod 2 = 0 Then 'average center two values
rs.MoveNext
fnMedian = Val(fnMedian) + Val(rs(0))
fnMedian = fnMedian / 2
End If
fnMedianExit:
If Not rs Is Null Then
rs.Close
Set rs = Nothing
End If
Exit Function
fnMedianError:
MsgBox Err.Number, Err.Description
GoTo fnMedianExit
End Function
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
Email address is not valid.
Please reply to newsgroup only.
:
I am very new to Access and would like to calculate the median of
various
groupings of data - I have no trouble grouping by average, min, max
etc. but
I don't know how to calculate the median. Any help would be much
appreciated.
TH

Here's a pure SQL approach that might work for you. This does not
calculate a proper mathematical median, but returns an actual value
from the middle of your ordered table. (It's virtually the same thing,
especially if the number of rows in the analysis is large).

This assumes you want the median RptPayLag based on specified values
in categories [Accident Year] and [Amount Code].

Note: after you save the SQL Access/JET will do wonky things with the
syntax. Save an original version of your SQL off line in a text file
just in case.

SELECT TOP 1 [Accident Year], [Amount Code], RptPayLag
FROM (SELECT * FROM
(SELECT TOP 50 PERCENT *
FROM PLDATA
WHERE [ACCIDENT YEAR] = [AY?] AND
[AMOUNT CODE] = [AMT CODE?]
ORDER BY RPTPAYLAG ASC)
ORDER BY RPTPAYLAG DESC);

You said it better than I did, and you are absolutely right. Thanks
for the idea of using MAX as well. Don't know why I didn't think of
that.

Just to explain my perceived applicability of the SQL approach, it
works well when:
- a large number of rows is analyzed and,
- a large number of repeating values are hovering around the median,
and
- the discrepancy where an even number of rows has two different
values at the middle does not return the mean of these values (but the
center-left value) is considered to be close enough

When I wrote the SQL I was processing thousands of rows of integer
values where 95% of the values were in the range 10..30. "Close
enough" was my call in this case, but of course individual situations
will vary. That said, the SQL proved to be much faster than the VBA
techniques I have seen and used elsewhere.