Spearmans Rank

[dodgymanc]

Is there a formula or function on Excel that allows me to calculate Spearmans Rank Correlation Coefficient?

 

[barkley]

Not too hot on the old Statistics front, was not my best module at Uni But you'll probably know more about the following excerpt than me:
"
Pearson or Spearman correlation?
If you choose correlation, choose between standard (Pearson) correlation and nonparametric (Spearman) correlation. Pearson correlation calculations are based on the assumption that both X and Y values are sampled from populations that follow a Gaussian distribution, at least approximately. With large samples, this assumption is not too important. If you don't wish to make the Gaussian assumption, select nonparametric (Spearman) correlation instead. Spearman correlation is based on ranking the two variables, and so makes no assumption about the distribution of the values.
"
http://www.graphpad.com/instatman/PearsonorSpearmancorrelation_.htm

So have a look at the PEARSON function in Excel and see if that can be of any use to you.

 

[Aladin Akyurek]

Spearman Rank Correlation Coefficient.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1				RankOf				Method1			Method2
2	X	Y		X	Y	d	d-squared	N	12
3	159	14.4		8	7	1	1		30		r-squared	0.800563873
4	179	15.2		7	5	2	4	r-Spearman	0.8951049		degreesoffreedom	10
5	100	11.3		9.5	8	1.5	2.25				Fs	40.14136675
6	45	2.5		12	11	1	1				p	0.00009
7	384	22.7		1	1	0	0
8	230	14.9		4	6	-2	4
9	100	1.41		9.5	12	-2.5	6.25
10	320	15.81		2.5	3	-0.5	0.25
11	80	4.19		11	10	1	1
12	220	15.39		5	4	1	1
13	320	17.25		2.5	2	0.5	0.25
14	210	9.52		6	9	-3	9
statspearman (3)

Formulas are...

D3:

=IF(ISNUMBER(A3),AVERAGE(RANK(A3,$A$3:$A$14),RANK(A3,$A$3:$A$14)+COUNTIF($A$3:$A$14,A3)-1),"")

E3:

=IF(ISNUMBER(Q3),AVERAGE(RANK(B3,$B$3:$B$14),RANK(B3,$B$3:$B$14)+COUNTIF($B$3:$B$14,B3)-1),"")

F3:

=D3-E3

G3:

=F3*F3

I2:

=COUNT(A3:A14)

I3:

=SUM(G3:G14)

I4:

=1-6*I3/(I2^3-I2)

L3:

=RSQ(E3:E14,D3:D14)

L4:

=COUNT(D3:D14)-2

L5:

=L4*(L3/(1-L3))

L6:

=FDIST(L5,1,L4)

For method 1, see Siegel (1956, p. 202) plus

http://www.zephryus.demon.co.uk/geography/resources/fieldwork/stats/spear.html#n6

and for Method 2, see...

http://udel.edu/~mcdonald/statspearman.html

I believe Method 2 is computationally easier and the formulas involved are insensitive to empty cells in X.

 

[jexcel3]

I use the spearman coefficient alot. I wanted to see if it is possilble for me to get help on creating an UDF that do this for me. I have attached this old thread to show what the full formula looks like.

My data source is a little differenct. I already have the ranked data in Column B and C

A1 Column title B1 Rank1 C1 Rank2
A2 XYZ B2 1 C2 3
A3 ABC B3 2 C3 1.5
A4 AAA B4 3 C3 1
etc etc etc

So i wanted to create a UDF in column D and be able filldown the udf and have the Correlation number in Column E. This number would be like the result in cell I4 in the example below. This might be to much for excel to handle..Any thoughts?

 

[jexcel3]

My data example did not post right.

Column A would be the managers
Column B is the first ranked data series
Column C is the second ranked data series
Column D is the UDF
Cell E2 would be the result

Sorry for the confusion

j

 

[Claymationator]

My data example did not post right.

Column A would be the managers
Column B is the first ranked data series
Column C is the second ranked data series
Column D is the UDF
Cell E2 would be the result

Sorry for the confusion

j

I am not sure if this is quite what you are looking for, but this has an example spreadsheet of how to calculate this with a dataset of up to 1000 observations.

http://udel.edu/~mcdonald/statspearman.xls

This site also has some information explaining the spreadsheet as well as a few other helpful links when calculating the spearman's rank.

http://udel.edu/~mcdonald/statspearman.html

Hope that helps.

 

[jexcel3]

thank you !!!!!

 

[Claymationator]

thank you !!!!!

You're welcome, and thanks to Aladin that had posted the link earlier in the thread.

and for Method 2, see...

http://udel.edu/~mcdonald/statspearman.html

 

[jexcel3]

Aladin,

Your example spreadsheet and formulas are very very very helpful!!!

I have one question. Spearman coef handles the ties by taking the average ie the 4s in the below example (8.5)

Data	Rank      Frmla
12.45	1	1.0
10.11	2	2.0
8.32	3	3.0
7.45	4	4.0
6.55	5	5.0
5.1	6	6.0
4.3	7	7.0
4	8	8.5
4	8	8.5
2.1	10	10.0
1.22	11	11.0

But when there are 3 fours in the below example, it rounds the rank 8...is that functioning correctly?...i think it is but i wanted to ask ya

Data 1	Rank	Frmla
12.45	1	1.0
10.11	2	2.0
8.32	3	3.0
7.45	4	4.0
6.55	5	5.0
5.1	6	6.0
4	7	8.0
4	7	8.0
4	7	8.0
2.1	10	10.0
1.22	11	11.0

 

[Aladin Akyurek]

Aladin,

Your example spreadsheet and formulas are very very very helpful!!!

I have one question. Spearman coef handles the ties by taking the average ie the 4s in the below example (8.5)

Data    Rank      Frmla
12.45    1    1.0
10.11    2    2.0
8.32    3    3.0
7.45    4    4.0
6.55    5    5.0
5.1    6    6.0
4.3    7    7.0
4    8    8.5
4    8    8.5
2.1    10    10.0
1.22    11    11.0

But when there are 3 fours in the below example, it rounds the rank 8...is that functioning correctly?...i think it is but i wanted to ask ya

Data 1    Rank    Frmla
12.45    1    1.0
10.11    2    2.0
8.32    3    3.0
7.45    4    4.0
6.55    5    5.0
5.1    6    6.0
4    7    8.0
4    7    8.0
4    7    8.0
2.1    10    10.0
1.22    11    11.0

Yes...

In the first situation, the consecutive rankings for 4 would be: 8 and 9 with average(8,9) yielding 8.5.

In the 2nd situtation, the consecutive rankings for 4 would be: 7, 8, and 9, with average(7,8,9) yielding 8.