Finding the Median

Discussion in 'Microsoft Access Queries' started by D Collins, Jul 9, 2003.

  1. D Collins

    D Collins Guest

    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, Jul 9, 2003
    #1
    1. Advertisements

  2. D Collins

    Sam Guest

    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" <> wrote in message
    news:082101c345b2$2adcbb30$...
    > 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, Jul 9, 2003
    #2
    1. Advertisements

  3. D Collins

    Jeff Boyce Guest

    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>
     
    Jeff Boyce, Jul 9, 2003
    #3
  4. D Collins

    Dale Fye Guest

    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" <> wrote in message
    news:...
    On Tue, 8 Jul 2003 17:36:42 -0700, "D Collins"
    <> 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, Jul 9, 2003
    #4
  5. D Collins

    Dale Fye Guest

    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" <> wrote in message
    news:...
    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" <> wrote in message
    news:082101c345b2$2adcbb30$...
    > 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.
     
    Dale Fye, Jul 9, 2003
    #5
  6. D Collins

    Tom Ellison Guest

    On Wed, 9 Jul 2003 04:54:21 -0700, "Jeff Boyce" <>
    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, Jul 9, 2003
    #6
  7. D Collins

    Tom Ellison Guest

    On Wed, 9 Jul 2003 04:55:47 -0700, "Jeff Boyce" <>
    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
     
    Tom Ellison, Jul 9, 2003
    #7
  8. D Collins

    D Collins Guest

    THANKS to ALL

    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, Jul 9, 2003
    #8
  9. D Collins

    D Collins Guest

    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" <> wrote in message
    >news:082101c345b2$2adcbb30$...
    >> 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, Jul 9, 2003
    #9
  10. D Collins

    Michel Walsh Guest

    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" <> wrote in message
    news:...
    > On Wed, 9 Jul 2003 04:54:21 -0700, "Jeff Boyce" <>
    > 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
     
    Michel Walsh, Jul 10, 2003
    #10
  11. D Collins

    Jeff Boyce Guest

    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>
     
    Jeff Boyce, Jul 11, 2003
    #11
  12. D Collins

    nitindeshpande

    Joined:
    Jul 30, 2012
    Messages:
    1
    Likes Received:
    0
    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
     
    Last edited: Jul 30, 2012
    nitindeshpande, Jul 30, 2012
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Hafner

    calling Excel function Median and Quartile

    Peter Hafner, Aug 28, 2003, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    286
    Peter Hafner
    Aug 28, 2003
  2. B Hand

    Determining the Median in an Access Field

    B Hand, Sep 16, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    181
    Michel Walsh
    Sep 17, 2003
  3. Don

    Median value in Pivot Table Queries

    Don, Oct 9, 2003, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    245
  4. Guest

    Finding the Median of a group of values

    Guest, Nov 5, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    232
    [MVP] S. Clark
    Nov 6, 2003
  5. Eric D. Braden

    Finding a Median

    Eric D. Braden, Oct 23, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    177
    Michel Walsh
    Oct 23, 2007
Loading...

Share This Page