Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Calculating the Weighted Median of a Recordset

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 1:04 pm    Post subject: Calculating the Weighted Median of a Recordset Reply with quote

To calculate the weighted median of a set of numbers you need to find the median and if this number does not exist in the recordset take the average of the values above and below the median instead.

Weighted Median of 1,2,3,4,5 is 3 (Median is also 3)
Weighted Median of 1,2,3,4,5,6 is 3.5 (Median is also 3.5)
Weighted Median of 1,2,4,4,4,7,7,8,8,8 is 5.2 (((4+4+4) + (7+7))/5) (Median is 5.5)

The function below shows you how to calculate the weighted median in access. Paste the following into a new or existing module and call it from anywhere to get the weighted median of a field in any recordset.

Please note there is no error handling so make sure the field is a valid number and the recordset exists and has one or more records.

The test sub shows you how to call it. This was built in the Northwind database so you can use it there for test purposes.

Code:
Public Function WeightedMedianOfRst(RstName As String, fldName As String) As Double
     'This function will calculate the weighted median of a recordset. The field must be a number value.
     Dim MedianTemp As Double
     Dim ThisValue As Double
     Dim NumRecs As Long
     Dim RstOrig As Recordset
     Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
     RstOrig.Sort = fldName
     Dim RstSorted As Recordset
     Dim RstFiltered As Recordset
     Set RstSorted = RstOrig.OpenRecordset()
     If RstSorted.RecordCount Mod 2 = 0 Then
          RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
          ThisValue = RstSorted.Fields(fldName).Value
          RstOrig.Filter = "[" & fldName & "] = " & ThisValue
          Set RstFiltered = RstOrig.OpenRecordset()
          MedianTemp = ThisValue * RstFiltered.RecordCount
          NumRecs = RstFiltered.RecordCount
          RstSorted.MoveNext
          ThisValue = RstSorted.Fields(fldName).Value
          RstOrig.Filter = "[" & fldName & "] = " & ThisValue
          Set RstFiltered = RstOrig.OpenRecordset()
          NumRecs = NumRecs + RstFiltered.RecordCount
          MedianTemp = MedianTemp + ThisValue * RstFiltered.RecordCount
          MedianTemp = MedianTemp / NumRecs
     Else
          RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
          MedianTemp = RstSorted.Fields(fldName).Value
     End If
     WeightedMedianOfRst = MedianTemp
End Function

Private Sub test()
     MsgBox MedianOfRst("Orders", "Freight")
End Sub

________
Health Shop
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group