Pearson Correlation r and p Values in Excel (CORRECTION IN DESCRIPTION)

CORRECTION:
In the video I used the following formula to calculate p values (=TDIST(ABS(([rValue]*SQRT([nSize]-2)/SQRT(1-([rValue]*[rValue])))), [nSize], 2)). The last [nSize] is the portion of the excel function where the degrees of freedom is entered. This should have been [nSize]-2. I accidentally left out the -2. Fortunately, it makes minimal difference with typical sample sizes. My quick testing suggests that it only matters at the 3rd decimal place with sample sizes of at least 10. Once over 20 participants it is a small enough difference that it would not impact reported data as the difference would be rounded away. Regardless, the formula given below has been corrected.


This video shows Dr. Evan Matthews explaining how to calculate the Pearson's correlation coefficient (r value) and the associated p value without using a statistical package within Microsoft Excel. This video is part of a playlist of videos showing how to set up a large research spreadsheet.


CORRECTED Pearson Correlation p Value Formula for Excel:
=TDIST(ABS(([rValue]*SQRT([nSize]-2)/SQRT(1-([rValue]*[rValue])))), [nSize]-2, 2)


Pearson’s Correlation Coefficient Interpretation
0 No Association
0.1 to 0.3 or −0.1 to −0.3 Small Effect
0.3 to 0.5 or −0.3 to −0.5 Medium Effect
0.5 to 1.0 or −0.5 to −1.0 Large Effect

Cohen J. Statistical Power Analysis for the Behavioral Sciences. 2nd. Mahwah: Lawrence Erlbaum Associates; 1988. https://doi.org/10.4324/97802 03771587


n Size video (Building in Statistics – Counting Sample Size Collected Example)
   • Building in Statistics – Counting Sample S...  

Conditional Formatting for Cells Based on Values or Text in Excel
   • Conditional Formatting for Cells Based on ...  


Link to Downloadable Dataset
https://digitalcommons.montclair.edu/...

Link to Published Study Using This Dataset
https://pubmed.ncbi.nlm.nih.gov/33417...


Master Research Spreadsheet Creation Playlist
   • Master Research Spreadsheet  


Excel Skills Playlist That Goes Beyond The Skills for Setting Up a Master Spreadsheet
   • Excel Microsoft Office  




Link to Dr. Evan Matthews website.
https://sites.google.com/site/evanmat...