Correlation Coefficient (w2k)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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:

Public Function xlCORREL(array1, array2) As Double

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

End Function

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

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

HTH

Reply | Quote

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:

• 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]”.
• 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.
• 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.

Reply | Quote

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

Reply | Quote