I'm not very professional in calculating Pearson's correlation coefficient (r), and I see people use the following formula to calculate the p value for r:
p=r/Sqrt(r^2)/(N—2)
where N is the number of variants.
why don't they just use student's t test? I usually use Excel and chose TTEST function for this matter. It's so easy.
This is a function specifically for calculating the Pearson correlation coefficient in Excel. It’s very easy to use. It takes two ranges of values as the only two arguments. = CORREL (Variable1, Variable2). While in excel, there isn’t a formula that gives a direct value of correlation’s associated P-Value. So we have to get P-Value from correlation, correlation is r for P-Value as we have discussed before, to find P-Valuepvalue we have to find after getting correlation for the given values.
Pearson's is used for determining if there is a correlation between variables, a t-test measures the difference between two populations, so they look at different things.
The formula you have given looks like it should be t=r/Sqrt((1-r^2)/(N—2)) the t statistic for testing if the correlation coefficient is significant, it can then be converted to a p value using the T.DIST function in Excel.
bob1 on Mon Apr 29 22:22:55 2013 said:
Pearson's is used for determining if there is a correlation between variables, a t-test measures the difference between two populations, so they look at different things.
I always use TTEST function and I have no problem. But the above equation gives me different p value which is not similar to the TTEST value. I am still not sure why people use this equation to calculate the p value of those variables. I hope you understand what I' trying to say. I know r is correlation coefficient. by why can't they just use the same TTEST, instead of this equation. it shouldn't be used?
DRT on Mon Apr 29 23:37:24 2013 said:
The formula you have given looks like it should be t=r/Sqrt((1-r^2)/(N—2)) the t statistic for testing if the correlation coefficient is significant, it can then be converted to a p value using the T.DIST function in Excel.
I checked TDIST. don't know how to fill those gaps.
Curtis on Tue Apr 30 05:35:18 2013 said:
DRT on Mon Apr 29 23:37:24 2013 said:
The formula you have given looks like it should be t=r/Sqrt((1-r^2)/(N—2)) the t statistic for testing if the correlation coefficient is significant, it can then be converted to a p value using the T.DIST function in Excel.
I checked TDIST. don't know how to fill those gaps.
I found this link with the instructions for getting the obtaining a p value from the correlation coefficient.
http://www.ehow.com/how_8554041_calculate-its-correlation-excel-2007.html
You may also like to install the ‘data analysis’ add-in for excel (under File-Options-Add-Ins) but make sure you come to grips with the different sorts of t-tests first Calculate Correlation
1. Open the workbook that contains your data in Excel 2007.
2. Determine the cell addresses that contain the two ranges of data that you want to compare. For example, if your data is contained in the first 20 rows of columns 'A' and 'B,' then the two cell address ranges for your data are 'A1:A20' and 'B1:B20.'
3. Click a cell that you want to use to calculate the Pearson correlation coefficient 'r.'
4. Type '=PEARSON(A1:A20,B1:B20)' -- without the quotes -- in the cell. Substitute the addresses of your data ranges in place of these sample ranges.
5. Press 'Enter.' Excel calculates the Pearson correlation coefficient for your data range and displays it in the cell.
Calculate P-Value
6. Click a different cell that you want to use to calculate the p-value that is associated with your correlation.
7. Copy and paste the following formula into the cell:=TDIST((pearson_cell*sqrt(N-2)/sqrt(1-(pearson_cell*pearson_cell))), N, 2)The cell returns an error message; don't worry, you'll fix the formula to work with your data array in the next steps.
8. Click the formula bar to edit the formula that you just pasted. Type the address of the cell that contains your Pearson correlation formula in place of all three instances of 'pearson_cell' in the above example. For instance, if you calculated the Pearson correlation in cell 'A22,' then the formula looks like this:=TDIST((A22*sqrt(N-2)/sqrt(1-(A22*A22))), N, 2)
9. Type the number of observations that you have for each variable in place of each instance of 'N' in the formula. For instance, if you have 20 observations in each column, then the formula now looks like this:=TDIST((A22*sqrt(20-2)/sqrt(1-(A22*A22))), 20, 2)
10. Press 'Enter.' The cell calculates and displays the p-value that is associated with your correlation.