Finding the Median

D Collins
Guest
Posts: n/a

 9th Jul 2003
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
Guest
Posts: n/a

 9th Jul 2003
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
> 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.

Jeff Boyce
Guest
Posts: n/a

 9th Jul 2003
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
Guest
Posts: n/a

 9th Jul 2003
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

Dale Fye
Guest
Posts: n/a

 9th Jul 2003
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
> 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.

Tom Ellison
Guest
Posts: n/a

 9th Jul 2003
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

Tom Ellison
Guest
Posts: n/a

 9th Jul 2003
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

D Collins
Guest
Posts: n/a

 9th Jul 2003
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.
>.
>

D Collins
Guest
Posts: n/a

 9th Jul 2003
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

For example,

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

>
>
>.
>

Michel Walsh
Guest
Posts: n/a

 10th Jul 2003
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Chris Barnett Microsoft Access 4 2nd Dec 2005 03:06 AM Andy Chan Microsoft Access 2 20th Nov 2005 02:10 AM =?Utf-8?B?R3JlZw==?= Microsoft Excel Worksheet Functions 2 13th Feb 2005 04:28 AM =?Utf-8?B?S2F0aGxlZW4=?= Microsoft Access Queries 1 6th Nov 2003 03:52 PM Jason Morin Microsoft Excel Worksheet Functions 0 1st Jul 2003 10:13 PM

Features