syntax for finding median value (1 Viewer)

[yyyoong]

Can anyone please help me to find the median values of 4 numbers given ?

 

[Jon K]

Try this function:-
----------------------------------
Public Function Median(a As Double, b As Double, _
c As Double, d As Double) As Double

Dim Smallest As Double
Dim Largest As Double

Largest = IIf(a > b, a, b)
Largest = IIf(Largest > c, Largest, c)
Largest = IIf(Largest > d, Largest, d)

Smallest = IIf(a < b, a, b)
Smallest = IIf(Smallest < c, Smallest, c)
Smallest = IIf(Smallest < d, Smallest, d)

Median = (a + b + c + d - Largest - Smallest) / 2
End Function
------------------------------------

I hope I have remembered the definition correctly.

If more than four numbers are given, it would be much more difficult.

 

[yyyoong]

To find median value

Thanks for showing the syntax, but i tried it in the queries, but it can't work. Actually my problem is: I have 4 fields (columns) in the queries, and intend to find the median values from the 4 columns, can i use the vb code in the 5th column in the queries?

 

[Jon K]

Open a new module. Type/paste the above function (from Public Function Median..... to End Function inclusive) in the module. Save the module as any name you like.


Then type/paste the following query in the SQL View of a new query (replacing with your table name and field names):-

SELECT [Field1], [Field2], [Field3], [Field4],
Median([Field1], [Field2], [Field3], [Field4]) AS Median
FROM TableName;

Run the query. The 5th column should give the Median of the four fields.

I have also attached a DB as a demo.

 

[raskew]

The following is a bit more generic, since it will take a list of values and return the median--it's not limited to a list of a specific size.

Function Medianx(ParamArray varNums() As Variant) As Variant
'*******************************************
'Name:      Medianx (Function)
'Purpose:   Return the median from a parameter
'           array of numbers
'Author:    raskew
'Inputs:    (1) ? medianx(1,11,8,3,6,13)
'           (2) ? medianx(1,11,8,3,6)
'Output:    (1) 7
'           (2) 6
'*******************************************

Dim Temp As Integer, i As Integer, J As Integer, n 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)

'For i = 0 To UBound(varNums)
'   Debug.Print varNums(i)
'Next i

End Function

Building a list of values within a query which can then be successfully fed to the Medianx() function is one I've yet to solve. Will be interested to see if someone can demonstrate a workable solution.

 

[yyyoong]

median value

Thanks to Jon K, by reading the attachment, i have an idea on how to do it.

 

[DJN]

There are a couple of articles on the Microsoft Knowledge bas that might help. They give usage as well.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q95918

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q103271

David