Calculating a p-value for the spearman's rank statistic in excel (using vba or not)

Question

For long reasons I cannot use SPSS or another statistical package for this part of the analysis. I have two questions.

Is there way of automatically ranking the data (from which i can later analyse). My variables go in one column (1,2,3,4,5,6....) which is already ranked by itself. My other variable can have a discrete value from 1 to 5 (e.g. 1 or 2 or 3 or 4 or 5). Even though in a way these are already ranked, it is not ranked in the way spearman wants. I think i could write some vba code to rank this myself, but if there was an automatic way it would be good to know!

Importantly, the only reason i would want to work out the spearman correlation is that it allows me to calculate a p value which is not biased from nonnormality. However, all the guides online tell me to use a table to see if the test value is below a certain level for P<.05 or whatever. How can i calculate a specific p value based on N and Rho in excel? im assuming there is a formula somewhere (I couldn't find one in my textbooks!).

Solution

Referring to the Wikipedia Example, we can use the formulas below to calculate the degrees of freedom (ν), Spearman rank correlation coefficient (ρ) and p-Value (using t-distribution).

x   y
106 7
86  0
100 27
101 50
99  28
103 29
97  20
113 12
112 6
110 17

ν    8             =COUNT(x)-2
ρ    -0.175757576  =CORREL(RANK.AVG(x,x,1),RANK.AVG(y,y,1))
Pval 0.686405828   =T.DIST(SQRT(ν*ρ^2/(1-ρ^2)),ν,1)

In the formulas above Defined Names have been used in place of cell references. Relative to A1 the references are: x=A2:A11, y=B2:B11, ν=B13, ρ=B14. (greek letters can be used via Insert > Symbol)

Note: This assumes Excel 2010/13 for the RANK.AVG / T.DIST functions (use rank_avg / t_dist in VBA)