Does anyone know the Visual Basic for: if the median is a tie, then pick
the higher of two values? Example: we do two-fold dilutions to test
antibiotics: .5, 1, 2, 4, 8, 16, 32, etc. If the median is evenly split
between 8 and 16, how can I program the results so it will pick the higher
value? Any help would be much appreciated. Thanks.
Here's a Median function that emerged from a thread here the other day.
I think that all you'll need to do to convert it to your
not-quite-median function is
1) Replace every occurrence of Median870 with another name.
2) Add a comment to make it clear that it handles "ties" in a non-
standard way and taking responsibility for the changed code
3) Change this line
Answer = (Answer + rst.Fields("Val").Value) / 2
to
Answer = rst.Fields("Val").Value
4) Test it.
Public Function Median870(ByVal SQL As String) As Variant
'SQL must be a Jet SQL SELECT statement query that returns
'fields Qty and Val ordered by Val ASC
'based on code posted by Tim Ferguson
'elaborated by John Nurick and
'refined with testing and input from Tom Wickerath
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstSums As DAO.Recordset
Dim MinVal As Long
Dim QtyTotal As Long
Dim QtySoFar As Long
Dim Answer As Variant ' or currency if you prefer
Set db = CurrentDb()
If Right(SQL, 1) = ";" Then
SQL = Left(SQL, Len(SQL) - 1)
End If
Set rst = db.OpenRecordset(SQL, dbOpenSnapshot, dbForwardOnly)
If rst.EOF Then
MsgBox "Empty recordset", vbExclamation + vbOKOnly
Median870 = Null
Exit Function
End If
Set rstSums = db.OpenRecordset("SELECT MIN(Val), SUM(QTY) FROM (" _
& SQL & ")", dbOpenSnapshot, dbForwardOnly)
MinVal = rstSums.Fields(0).Value
QtyTotal = rstSums.Fields(1).Value
Answer = Null
rstSums.Close
Do While IsNull(Answer)
' we should never see the end of the recordset
If rst.EOF Then Err.Raise 9999, , "Someone has blundered!"
QtySoFar = QtySoFar + rst.Fields("Qty").Value
If QtySoFar * 2 > QtyTotal + 1 Then
' if we've gone past, we don't need to worry about interpolating
Answer = rst.Fields("Val").Value 'wrap in CDbl()
'or CCur() if desired
ElseIf QtySoFar * 2 = QtyTotal + 1 Then
' okay, it's exactly the middle one out of an odd
' number so we've got the answer
Answer = rst.Fields("Val").Value
ElseIf QtySoFar * 2 = QtyTotal Then
' this is the first of the middle pair; we need to
' average this Val and the next one
Answer = rst.Fields("Val").Value
rst.MoveNext
If rst.EOF Then
'The middle item is in the last record, so the
'median Val is also the maximum Val
'and Answer is already set to the right value
Else
Answer = (Answer + rst.Fields("Val").Value) / 2
End If
Else
' no joy yet, just get the next record and move on
rst.MoveNext
End If
Loop
rst.Close
' return Answer (use CDbl() or CCur() if desired)
Median870 = Answer
End Function
