Significance of Correlation

**tayto** · 10-30-2006, 11:13 AM

Hello,

I am in the unfortunate position of having to run about 900 correlations with Excel as my only option. Normally this would be fine, but in this case, I need the Pearson's test of significance. The only way I know how to obtain this in Excel is through using a regression, which is incredibly time-consuming. Does anyone have a suggestion how to make the best use of time?

Regards.

**MrShorty** · 10-30-2006, 12:21 PM

Will the PEARSON worksheet function work?

**tayto** · 10-30-2006, 03:30 PM

Originally Posted by MrShorty

Will the PEARSON worksheet function work?

Thanks for the input. Unfortunately the PEARSON function only returns the Pearson Coefficient and not the level of significance.

**Carim** · 10-30-2006, 03:34 PM

Hi,

Did you read the Help for the TDIST function ?

HTH
Carim

**MrShorty** · 10-30-2006, 04:04 PM

Not being a statistician, I'm not entirely certain what you are looking for to get the "Pearson test of significance" or "level of significance." Excel help refers specifically to Pearson in two places that I could find, the PEARSON function and the RSQ function (which appears to simply be PEARSON^2).

My statistics text associates the Pearson statistic with the Chi Squared test, and Excel has a few built in functions based on the Chi squared test. Perhaps one of those is what you're after?

How do you normally do regressions, and how do you get this "significance" from that? Excel has quite a few built in statistical functions, and I expect that some combination of those is what you're looking for.

**tayto** · 10-30-2006, 09:21 PM

Thank you both for your help.

The T-distribution will not help here, unfortunately.

While there is a Pearson's Chi-Square test, the test I am looking for in this case is the Pearson's r test of significance. This value is the exact same as a p-value in a regression.

Normally I do this work in SAS, which can run the 900 correlations in about 5 seconds.

I think I am seeing the need to get a package better than Excel.

**Dav** · 10-31-2006, 05:47 AM

without knowing your data

t=r x sqrt(n-2)/sqrt(1-r x r)

where r is the calculated correlation coefficient n is the number of observations, x is just multiplied

This corresponds to a t statisitc with n-2 degrees of freedom

if your n varies by missing observations etc you could use
sumproduct((a1:a100>0)*(b1:b100>0)) to determine n

or

sumproduct((isnumber(a1:a100))*(isnumber(b1:b100)))

This is easy to calculate in excel

You can calculate t from the above formula and with tdist or tinv functions you can get your result, depending on if you want an exact probability or to highlight the results significant at a given level

SAS is easier, but it is a stats package and costs lots more money! but it would not take long to do in excel, probably 10 minutes to set up in the first place for all the correlations and the test.

Regards

Dav

**tayto** · 10-31-2006, 11:36 AM

Didn't think of that at all, Dav. Thanks a bunch.

Yeah, SAS is certainly expensive, but I have been hearing that JMP is a nice option for a significantly lower price.

**Dav** · 10-31-2006, 12:00 PM

Your only thing you need to watch is the dealing with missing values, that you define a text value and do not leave them as blank, becasue this may cause them to be treated as 0's

SPSS is cheaper than SAS and a comprehensive product, but it depends what you want to do and what you want to pay. I have not looked at stats packages for a long time, apart from SPSS as that is what we use at work. Often there are addons to Excel that are usuable, for a small fee

I did the following and forgot to post it, to make sure my 10 minutes was not a lie!

Regards

Dav

**tedladd** · 02-16-2007, 01:23 AM

Dav, I applaud your thoroughness, but your method didn't work for me!

I seek a single number that gives me the significance of a correlation, expressed as a percentage, most closely related to a "p-value" for the correlation.

I can achieve this using the Regression analysis from the Data Analysis... tool. It is the p-value associated with the slope, not the intercept. And it happens to be the same number as the Significance F in the ANOVA.

My problem is that, using your method, I cannot reproduce the ANOVA F or Significance F. (If I have the former, I can get the latter with =FDIST.) Nor can I reproduce the t stat or the p-value. (Again, with the former I can get the latter.) Nor can I figure out a way to generate the coefficient and standard error for the X term (coefficient/SE = t stat).

I've found no help in LINEST, TINV, FINV, TTEST, or FTEST. None of these functions can reproduce the same numbers from the Regression output. Can you think of a way using built-in formulae or equations to get me there?

PS This is part of a sheet that is being "webified" using .NET, so I can't use the Regression tool itself.

**Tiresidedown** · 01-15-2010, 01:57 AM

Hello,

I have run regression in Excel, but I'm not seeing a way to determine significance. Can someone point me in the right direction?

Thank you.

Dan

**DonkeyOte** · 01-15-2010, 04:17 AM

Tiresidedown,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

I see you have done this but going forward please avoid the above...