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

3 comments:

  1. UnknownDecember 5, 2018 at 4:57 PM

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

    ReplyDelete
  2. StevenGreenApril 28, 2023 at 8:54 AM

    If you've had a negative experience with outsourcing, it shouldn't lead you to draw hasty conclusions. Although outsourcing can bring numerous advantages, it also poses certain risks and challenges. Effective communication and collaboration between the company and service provider is essential, particularly when working with geographically dispersed teams. If the service provider is not managed correctly, there is also a risk of quality issues or delays. However, all of these difficulties can be resolved by collaborating with a highly skilled team, look what i found by the link provided here.
    Source: https://mobilunity-bpo.com/text-annotation-services/

    ReplyDelete
  3. StevenGreenApril 28, 2023 at 8:55 AM

    If you've had a negative experience with outsourcing, it shouldn't lead you to draw hasty conclusions. Although outsourcing can bring numerous advantages, it also poses certain risks and challenges. Effective communication and collaboration between the company and service provider is essential, particularly when working with geographically dispersed teams. If the service provider is not managed correctly, there is also a risk of quality issues or delays. However, all of these difficulties can be resolved by collaborating with a highly skilled team, look what i found by the link provided here.

    ReplyDelete

Subscribe to: Post Comments (Atom)