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.

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

"D Collins" <(E-Mail Removed)> wrote in message
news:082101c345b2$2adcbb30$(E-Mail Removed)...
> 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.

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>

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

--
HTH

Dale Fye


"Tom Ellison" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Tue, 8 Jul 2003 17:36:42 -0700, "D Collins"
<(E-Mail Removed)> wrote:

>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

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


"Sam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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

"D Collins" <(E-Mail Removed)> wrote in message
news:082101c345b2$2adcbb30$(E-Mail Removed)...
> 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.

On Wed, 9 Jul 2003 04:54:21 -0700, "Jeff Boyce" <(E-Mail Removed)>
wrote:

>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

On Wed, 9 Jul 2003 04:55:47 -0700, "Jeff Boyce" <(E-Mail Removed)>
wrote:

>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

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.
>-----Original Message-----
>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.
>.
>

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.

>-----Original Message-----
>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
>
>"D Collins" <(E-Mail Removed)> wrote in message
>news:082101c345b2$2adcbb30$(E-Mail Removed)...
>> 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.
>
>
>.
>

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


"Tom Ellison" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 9 Jul 2003 04:54:21 -0700, "Jeff Boyce" <(E-Mail Removed)>
> wrote:
>
> >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