Finding the Median

[D Collins]

Hello All,

I need to find the median of a series of numbers. I
thought there was a function to find the median--there is
in Excel. Is Access capable of finding the median of a
series of numbers?

Thanks.

 

[Sam]

Does seem odd that a function is not included in Access, I need the median
all the time so wrote one for my own use that simulates the other domain
functions. Here's the code for a DMedian function I use all the time to find
the median....

Public Function DMedian(Expr As String, Domain As String, Optional Criteria
As String) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Temp As Double
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

rs.MoveLast
NumRecords = rs.RecordCount
rs.MoveFirst
rs.Move Int(NumRecords / 2)
Temp = rs.Fields(Expr)

If NumRecords / 2 = Int(NumRecords / 2) Then
'there is an even number of records
rs.MovePrevious
Temp = Temp + rs.Fields(Expr)
DMedian = Temp / 2
Else
'there is an odd number of records
DMedian = Temp
End If

rs.Close
db.Close

Once pasted into a module a call would something like...
=DMedian("MyNumericFieldName","MyTable","MyCriteria")

HTH
Sam

 

[Jeff Boyce]

Hi Tom!

As a recovering statistician, I'd offer the notion that the median of a even
number of elements is the average of the two "center-most".

Jeff Boyce
<Access MVP>

 

[Dale Fye]

Actually Tom, if there are an even number of elements, then you are
supposed to average the center most values.

--
HTH

Dale Fye

Hello All,

I need to find the median of a series of numbers. I
thought there was a function to find the median--there is
in Excel. Is Access capable of finding the median of a
series of numbers?

Thanks.

Dear D:

There is no such function for Access, but a query is capable of
performing this.

I created a table I called Number and put one column in it with digits
1-9, then wrote this query:

SELECT Number
FROM Number N
WHERE (SELECT COUNT(*) FROM Number N1 WHERE N1.Number < N.Number) =
(SELECT COUNT(*) FROM Number N2 WHERE N2.Number > N.Number)

The result was 5.

If the table has an even number of rows in it (say 1-8) then there is
no result. This is correct.

Perhaps you can use something similar for your needs.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Dale Fye]

Sam,

I have a similiar function. To bring your in line with the other SQL
functions, you might want to consider adding to your sql to exclude
null values, or maybe add an optional parameter that indicates whether
to include null values, since most SQL functions exclude nulls from
calculations.

--
HTH

Dale Fye


Does seem odd that a function is not included in Access, I need the
median
all the time so wrote one for my own use that simulates the other
domain
functions. Here's the code for a DMedian function I use all the time
to find
the median....

Public Function DMedian(Expr As String, Domain As String, Optional
Criteria
As String) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Temp As Double
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

rs.MoveLast
NumRecords = rs.RecordCount
rs.MoveFirst
rs.Move Int(NumRecords / 2)
Temp = rs.Fields(Expr)

If NumRecords / 2 = Int(NumRecords / 2) Then
'there is an even number of records
rs.MovePrevious
Temp = Temp + rs.Fields(Expr)
DMedian = Temp / 2
Else
'there is an odd number of records
DMedian = Temp
End If

rs.Close
db.Close

Once pasted into a module a call would something like...
=DMedian("MyNumericFieldName","MyTable","MyCriteria")

HTH
Sam

 

[Tom Ellison]

Hi Tom!

As a recovering statistician, I'd offer the notion that the median of a even
number of elements is the average of the two "center-most".

Jeff Boyce
<Access MVP>

Dear Jeff:

This is very true except . . .

I was preparing for the other shoe to drop. Not infrequently, the
request is to show the entire record that is at the median, not just
the numeric value of the median. If there are an even number of rows
with distinct median values, then there is no median row because, if
you average the values in the two rows at the center you would get a
value that doesn't exist in the table.

There are variations of what to show depending on uniqueness of the
values and whether there are an odd or even number of values, all of
this depending on the needs for the situation at hand.

The query I gave was what I use for a starting point to prepare any of
these variations. As a starting point, I'll stand by it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

One approach is to open a code module, then use Tools | References to set a
reference to the Excel object model. You can then use a procedure to call
the Median() function from Excel.

Good luck

Jeff Boyce
<Access MVP>

Dear Jeff:

I'm not sure I follow how this could work. A median function would
seem to be a type of aggregate function when applied to a database.
An Excel function isn't going to work as an aggregate in any way I've
seen.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[D Collins]

Wow,

Didn't know that this topic would have generated so much
discussion. I used the function procedure provided and
it works great!

Thanks to everyone!

D.

 

[D Collins]

Hi Sam,

Thanks for the function. It works just fine for the
entire recordset, but, I was wondering how I should be
entering the criteria. I need to find the median based
on the type of trade.

For example,
[txtTrade] = "Carpentry"

Do I need to pass this criteria in a certain manner when
I call the function?

I've tried different variations and it keeps giving me an
error.

Thx.

 

[Michel Walsh]

Hi Tom,

It does not work with duplicated values: with 1 2 2 4 5 the answer should be 2.


If you want to return a value present in the set, you can define a new median, medianEx, that
return the min value producing a count of at least 50% of all the values below or equal to it:

SELECT Min(Number) As MedianEx
FROM Number As N
WHERE (SELECT COUNT(*) FROM Number As N1 WHERE N1.Number <= N.Number)

= 0.5*(SELECT COUNT(*) FROM Number)

which return a value with odd number of value, even number of value or duplicated values, and a
result "close" to what the classical median is.


Vanderghast, Access MVP

 

[Jeff Boyce]

Tom

I'm probably off base, then. I was imagining something like filling an
array with values, then running the median function against the array.

Jeff Boyce
<Access MVP>

 

[nitindeshpande]

Here is a code for DMEDIAN function I wrote that works exactly with similar parameters such as other domain functions. I wrote this in Excel 2010, hope this helps.

Function DMEDIAN(Database, Field, Criteria)
On Error GoTo ErrorHandler
ReDim dati(Database.Rows.Count)
ReDim crtable(rcount, ccount)

ccount = Criteria.Columns.Count
rcount = Criteria.Rows.Count
dccount = Database.Columns.Count
drcount = Database.Rows.Count

'Find the Field index in Database
For i = 1 To dccount
If Database(1, i) = Field Then
iField = i
End If
Next i

cdati = 0
For i = 2 To drcount
For a = 2 To rcount
ok = True
bAtleastOneMatch = False
For j = 1 To dccount
If Database(i, j) = Empty Then
Exit For
End If
fNum = False
If IsNumeric(Database(i, j)) Then
fNum = True
End If

fdate = False
If IsDate(Database(i, j)) Then
fdate = True
End If

For b = 1 To ccount
If Criteria(a, b) <> Empty Then
If Criteria(1, b) = Database(1, j) Then
bAtleastOneMatch = True
Select Case Left(Criteria(a, b), 1)
Case "="
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 2))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 2))
Else
ddata = Mid(Criteria(a, b), 2)
End If
If Database(i, j) <> ddata Then
ok = False
Exit For
End If
Case ">"
If Left(Criteria(a, b), 2) = ">=" Then
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 3))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 3))
Else
ddata = Mid(Criteria(a, b), 3)
End If
If Database(i, j) < ddata Then
ok = False
Exit For
End If
Else
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 2))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 2))
Else
ddata = Mid(Criteria(a, b), 2)
End If
If Database(i, j) <= ddata Then
ok = False
Exit For
End If
End If
Case "<"
If Left(Criteria(a, b), 2) = "<>" Then
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 3))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 3))
Else
ddata = Mid(Criteria(a, b), 3)
End If
If Database(i, j) = ddata Then
ok = False
Exit For
End If
ElseIf Left(Criteria(a, b), 2) = "<=" Then
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 3))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 3))
Else
ddata = Mid(Criteria(a, b), 3)
End If
If Database(i, j) > ddata Then
ok = False
Exit For
End If
Else
If fNum Then
ddata = CLng(Mid(Criteria(a, b), 2))
ElseIf fdate Then
ddata = CDate(Mid(Criteria(a, b), 2))
Else
ddata = Mid(Criteria(a, b), 2)
End If
If Database(i, j) >= ddata Then
ok = False
Exit For
End If
End If
End Select
End If
End If
Next b
If ok = False Then
Exit For
End If
Next j
If ok = True And bAtleastOneMatch = True Then 'Add the number for median calculation
cdati = cdati + 1
dati(cdati) = Database(i, iField)
Exit For
End If
Next a
Next i

'Sort the dati array in ascending order before calculating the Median
For i = 2 To cdati
For j = 1 To i - 1
If dati(j) > dati(i) Then
Tmp = dati(j)
dati(j) = dati(i)
dati(i) = Tmp
End If
Next j
Next i

'Calculate Median
If cdati Mod 2 = 1 Then
DMEDIAN = dati(Int(cdati / 2) + 1)
Else
DMEDIAN = (dati(cdati / 2) + dati(cdati / 2 + 1)) / 2
End If
Exit Function

ErrorHandler:
MsgBox "Error at DRow = " + i + ", DCol = " + j + ", CRow = " + a + ", CCol = " + b + ", Database = " + Database(i, j) + ", Criteria = " + Criteria(a, b)
End Function