Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Correlation Coefficient (w2k)

    Hello,

    I am trying to calculate the correlation coefficient in a report. I looked in help which told me to use correl(array1,array2). So I tried the following: =Correl([netreturn],[netreturn1]) in the report. (Netreturn and netreturn1 are series of numbers that are on the report.) Access does not seem to be understanding Correl.

    Can anyone help me? Am I providing enough information?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Correlation Coefficient (w2k)

    AFAIK, Correl is an Excel worksheet function, not a standard VBA or Access function. Note: I'm using Access 97, so if Correl is available in Access 2000, other Loungers please correct me).

    So, you must either use the Excel function by creating an Excel.Application object (you'll find how to do this if you search for Excel Automation), or use a custom function. I don't have one available; hopefully a statistically inclined Lounger will provide one.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Correlation Coefficient (w2k)

    As HansV noted there is no CORREL function available in Access, you'd have to use the Excel worksheet function (unless you are smart enough to write your own function that performs this calculation - not me). According to Help, this function "returns the correlation coefficient between two data sets." Example of how to use in Access:
    <pre>Public Function xlCORREL(array1, array2) As Double

    Dim xl As New Excel.Application
    xlCORREL = xl.WorksheetFunction.CORREL(array1, array2)
    Set xl = Nothing

    End Function</pre>

    <pre>Sub TestCorrelFunction()

    Dim varArray1 As Variant
    Dim varArray2 As Variant

    varArray1 = Array(1, 2, 3, 4, 5)
    varArray2 = Array(1, 3, 5, 9, 10)

    Debug.Print xlCORREL(varArray1, varArray2)

    End Sub</pre>


    PS - Each array must have same number of elements or an error will occur. But you probably already knew that.

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Correlation Coefficient (w2k)

    If you want to calculate the correlation coefficient of the values of two numeric fields in a table or query (for instance the record source of the report), the method using Excel requires that you first put all values of each field into an array. You'd have to write code to do that, and performance may be slow.

    Here is a custom function that uses the statistical domain functions. It can no doubt be improved; any error now forces the function to return 0. The function should be placed in a standard module.

    Public Function Correl _
    (sField1 As String, _
    sField2 As String, _
    sRecordSet As String, _
    Optional sCondition As String = "") As Double

    Dim dblAvgField1 As Double
    Dim dblAvgField2 As Double
    Dim dblAvgProduct As Double
    Dim dblStDevField1 As Double
    Dim dblStDevField2 As Double

    On Error GoTo Err_Correl
    dblAvgField1 = DAvg(sField1, sRecordSet, sCondition)
    dblAvgField2 = DAvg(sField2, sRecordSet, sCondition)
    dblAvgProduct = DAvg(sField1 & "*" & sField2, sRecordSet, sCondition)
    dblStDevField1 = DStDevP(sField1, sRecordSet, sCondition)
    dblStDevField2 = DStDevP(sField2, sRecordSet, sCondition)
    Correl = (dblAvgProduct - dblAvgField1 * dblAvgField2) / (dblStDevField1 * dblStDevField2)

    Exit Function

    Err_Correl:
    Correl = 0
    End Function

    The function has four arguments, of which the last is optional:
    <UL><LI>sField1 and sField2 are the names of the fields for which you want to calculate the correlation coefficient. They must be numeric. If the names contain spaces, you must put square brackets around them, for instance "[June Sales]".
    <LI>sRecordSet is the name of the table or query that contains the fields. The table or query must exist in the database, you can't specify a SQL statement.
    <LI>sCondition is optional; use it to specify criteria if you want to limit the records used in the calculation. As in the statistical domain functions, it is equivalent to the WHERE part of a SQL statement (without the word WHERE itself). If you omit sCondition, the calculation includes all records in sRecordset.[/list]Note: the function uses the population standard deviation StDevP, so it calculates the "population" correlation coefficient. If you want the "sample" version, replace StDevP by stDev in both occurrences.

    Examples of usage:

    1) You have a report based on a table tblCars with fields Brand, Sale_Price and Max_Speed. You want the correlation between price and speed. Create a text box in the footer section of the report, and set its control source to

    =Correl("[Sale_Price]", "[Max_Speed]", "tblCars")

    2) You have grouped your report by Brand, and want the correlation between price and speed for each brand. Create a text box in the group footer section for Brand, and set its control source to

    =Correl("[Sale_Price]", "[Max_Speed]", "tblCars", "[Brand]=" & Chr(34) & [Brand] & Chr(34))

    The Chr(34) puts double quotes around the brand name.

    Small print: Although I checked the result of the function in a few simple cases, I can't guarantee that it returns correct answers under all circumstances. I will not be held responsible for incorrect results. Loungers wanting to use this function should, if possible, check the result.

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Correlation Coefficient (w2k)

    Thank you Hans and Mark for your input. This helps a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •