We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How to Calculate the P-Value & Its Correlation in Excel 2007

Updated February 21, 2017

Calculating the correlation coefficient between two data arrays is a simple process in Excel 2007 that requires just a single formula function. Calculating the p-value, or probability of significance, that is associated with that correlation coefficient is a slightly more involved task: you must input a formula that first calculates the t-value associated with the correlation, then use that value to calculate and display the p-value.

Loading ...
  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.

  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:

  8. \=TDIST((pearson_cell_sqrt(N-2)/sqrt(1-(pearson_cell_pearson_cell))), N, 2)

  9. The cell returns an error message; don't worry, you'll fix the formula to work with your data array in the next steps.

  10. 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:

  11. \=TDIST((A22_sqrt(N-2)/sqrt(1-(A22_A22))), N, 2)

  12. 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:

  13. \=TDIST((A22_sqrt(20-2)/sqrt(1-(A22_A22))), 20, 2)

  14. Press "Enter." The cell calculates and displays the p-value that is associated with your correlation.

Loading ...

About the Author

Dan Howard is a sports and fitness aficionado who holds a master's degree in psychology. Howard's postgraduate research on the brain and learning has appeared in several academic books and peer-reviewed psychology journals.

Loading ...