P-values for correlations in Excel

This is just a quick post to describe how to calculate p-values for two-variable correlations in Excel. Annoyingly, there is no direct way of doing this. Excel will give you the correlation, but not its associated p-value. It can be done, however, in a slightly roundabout way.

First, calculate the correlation between your groups:

=correl(variable1, variable2)

This gives you the sample test statistic r, which can be converted to t with the following formula:

temp2

where r is the correlation obtained above and n is your number of observations. Say you have 30 samples for two groups, and a r of 0.5. The calculation to obtain t is then (in excel terms):

=(0.5*sqrt(30-2))/(sqrt(1-0.5^2))
=3.05505

Then to assess the significance value associated with this t, simply use the tdist function (Student T distribution output):

=t.dist.2t(t, degrees of freedom)

This gives us results for a two-tailed distribution. Alternatively, the old tdist function can still be used, which requires the user to specify the number of tails (=tdist(t, degrees of freedom, #tails)).

Our calculation thus looks like this:

=t.dist.2t(3.05505, 30-2)
=0.0049

Which is the p-value for the correlation. Done!

excel-2010-logo

Advertisements

One thought on “P-values for correlations in Excel

  1. Pingback: P-values for correlations in Excel — physiology and coffee | SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s