Calculating a Correlation Coefficient

[mdarren]

I have two sets of data on which I need to calculate the correlation coefficient on a report. In Excel, this would be =Correl(B3:B25,D325) However, I can't seem to find a similar built-in Correl function in Access and, not being a math wizard, I can't seem to decipher the various examples I've found on the web on the topic. Does anyone know if a Correl function exists in Access and, if so, what is the syntax? Many thanks.

 

[VogonPoet]

I think you may need to become a math wiz. Listed below is a list to a web page that gives the formala for calculating the correlation coefficient:

http://fonsg3.let.uva.nl/Service/Statistics/Correlation_coefficient.html

R = { Sum( x * y ) - Sum(x) * Sum / N } /
sqrt( {Sum( x**2 ) - Sum( x )**2 / N} * {Sum( y**2 ) - Sum( y )**2 / N} )


I think I converted the above into an Access control. You should just need to replace the X's and Y's with the field names.

sum(x * y) - sum(x) * sum/count(x)/
((sum(x^2) - sum(x)^2/count(x)) - (sum(y^2) - sum^2/count))^(1/2)

Good Luck,
Michael

 

[MichaelRed]

Hmmmmmmmmm,


Interesting?

Two random thoughts.

One randomness is to just "use" the excel function directly from within access. To be able to use the excel functions, all you need to do is add a reference to the (appropiate) excel library. See Tools.References and possably help (in Ms. A)re "references". Of course, you MAY also need to understand "how" to instantiate the function and its' arguments.

Next thought. It is almost always better to inplement integer powers as simple multiplication. X * X = X ^ 2, but X * X is MUCH faster. For simplistic processes, the user will not notice the difference. For large scale production processes, the difference is significant.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[mdarren]

Thank you for your advice. I'll give it a shot. If it doesn't work, I may keep the query and push this function back to the DBA to perform in Excel.

Matthew

 

[MichaelRed]

??????????? " ... back to the DBA to perform in Excel." ???

WHY is the DBA responsible for functions in excel???

What organization is this?

Just curious?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Guest_imported]

Here's the correct one,..

(Sum(x * y) - Sum(x) * Sum / Count(x)) /
((Sum(x^2) - Sum(x)^2 / Count(x)) * (Sum(y^2) - Sum^2 / Count))^(1/2)