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:

correlation, pearson correlation, r critical, statistics, excel, excel 2010, excel 2013(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:

correlation, pearson correlation, r critical, statistics, excel, excel 2010, excel 2013(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:

correlation, pearson correlation, r critical, statistics, excel, excel 2010, excel 2013(Click On Image To See Larger Version)

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:

correlation, pearson correlation, r critical, statistics, excel, excel 2010, excel 2013(Click On Image To See Larger Version)

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

 

Posted by at

1 comment:

  1. UnknownDecember 5, 2018 at 4:57 PM

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

    ReplyDelete

Subscribe to: Post Comments (Atom)