Inconveniently, this is not completely straightforward - Excel will not give us the exact p-value for any value of r. However, it will give the exact p-value for any value of t, and it’s not too hard to convert r to t.
The formula you need is this one:
And then you use the tdist() function in Excel.
So, we have a value of r = 0.44, and N = 19.
We can use Excel to turn the r into t, so in the Excel sheet (at Cell A1, let’s say) we type:
=(0.44 * sqrt(19 – 2)) / (sqrt(1-0.44^2))
This gives a value of t = 2.02.
We then use the tdist() function to find the associated p. We need to tell Excel 3 things. First, the value of t, second, the degrees of freedom, which are equal to N – 2 = 17, and third, the number of tails – either 1 or 2, and we always use 2 tails.
If the value from the first calculation is stored in cell A1, we can write:
=tdist(A1, 17, 2)
Which gives a result of p = 0.059.
Should you ever want to calculate a critical value for a Pearson correlation, the process is reversed. You first calculate the critical value for t, and then you convert this into r.
Let’s say we wanted to know the critical value for a correlation for p = 0.05. We first find the value of t that gives a p of 0.05. We use the excel function tinv(). We need to tell Excel two things, the probability that we are interested in, and the degrees of freedom. Into cell A1 We type:
=tinv(0.05, 17)
Excel tells us that the answer is 2.11.
We then need to turn that into a value of r. The formula is the reverse of the one above, which takes a bit of algebra, so we’ll tell you what it is:
We type the formula into Excel
=A1/(SQRT(A1 * A1 + 19 - 2 ))
And we get the answer that the critical value is 0.0456.