## Wednesday, May 28, 2014

### Pearson Correlation - r Critical and p Value of r in Excel

This is one of the following four articles on Correlations in Excel

Overview of Correlation In Excel 2010 and Excel 2013

Pearson Correlation in 3 Steps in Excel 2010 and Excel 2013

Pearson Correlation – Calculating r Critical and p Value of r in Excel

Spearman Correlation in 6 Steps in Excel 2010 and Excel 2013

# Pearson Correlation Step 3 – Determine Whether r Is Significant

After calculating the Pearson Correlation Coefficient, r, between two data sets, the significance of r should be checked. If r has been calculated based upon just a few pairs of numbers, it is difficult to determine whether this calculated correlation really exists between the two sets of numbers or if that calculated r is just a random occurrence because there are so few data pairs.

On the other hand, if the r is calculated from a large number of data pairs, the certainty level is much higher the calculated correlation r really does exist between the two sets of numbers.

There are two equivalent ways to determine whether or not the calculated r should be considered significant at a given α. These two methods are the following:

a) Calculate the p value and compare it to the specified α

b) Calculate r Critical and compare it to r

## Calculate p Value

To find the p Value for a given r and sample size, use the following formula: (Click On Image To See Larger Version)

p Value = 1 - F.DIST( ((n-2)*r^2)/(1-r^2), 1, n-2 )

df = n - 2

n = number of X-Y data pairs

The p value can be directly compared to Alpha to determine if the calculated correlation coefficient is statistically significant.

For example, if Alpha is set to 0.05, the p Value must be less than 0.05 to be considered statistically significant. If the p Value is less than 0.05, you can be at least 95% certain that the calculated correlation value was not a random event.

The calculation in Excel for this example is performed as follows:

p Value = 0.0008 =1-F.DIST(((7-2)*0.0.9544^2)/(1-0.9455^2),1,7-2,TRUE)

The p Value of 0.0008 is much less than alpha (0.05). This indicates that r is significant.

## Calculate r Critical

r Critical is the minimum value of r that would be considered significant for a given sample size and alpha level. r Critical is usually looked up on a chart but can be calculated directly with the following Excel formula: (Click On Image To See Larger Version)

For a small number a data pairs, the calculated r must be very high to be reasonably certain that this calculated correlation really does exist between the two variables and is not just a random occurrence.

The calculation in Excel is performed as follows:

r Critical = 0.7545 =(T.INV(1-0.05/2,7-2))/SQRT((T.INV(1-0.05/2,7-2))^2+7-2)

r Critical(α= 0.05, df = n-2 =5) = 0.7545

The correlation coefficient r (0.9544) is much greater than r Critical (0.7545). This indicates that r is significant.

### Comparing Chart Values of r Critical and p value in Excel with Calculated Values

Charts containing r Critical values list the following r Critical value for α = 0.05 and sample size n = 10 as follows:

r Critical(α= 0.05, df = n-2 =8) = 0.632

r Critical and the p value will now be calculated by the formulas to verify that chart values for r Critical match those calculated with the formulas.

### Calculating r Critical with the Formula

Plugging values α = 0.05 and df = 8 into the r Critical formula produces the following result:

The calculation in Excel is performed as follows:

r Critical =(T.INV(1-0.05/2,10-2))/SQRT((T.INV(1-0.05/2,10-2))^2+10-2) = 0.632

### Calculating p Value With the Formula

The p Value for the r Critical with df = n – 2 = 8 should be 0.05. Plugging that r Critical and df value into the p value formula produces the following result:

The calculation in Excel is performed as follows:

p Value =1-F.DIST(((10-2)*0.632^2)/(1-0.632^2),1,10-2,TRUE) = 0.05

The value of r Critical for Alpha = 0.05 equals 0.632. This agrees with the value calculated with the r Critical formula.

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP

#### 1 comment:

1. Thank you so much for putting in the excel formulas, you are a merciful lord