User Defined Function for Spearman's Rank Correlation Coefficient

[edwinsampang]

Is there a VBA code for the Spearman's Rank Correlation Coefficient, where its syntax is
=Spearman(Range1, Range2)?

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

Function Spearman(Rng1 As Range, Rng2 As Range) As Double
    Dim WF As WorksheetFunction
    Dim dSquared() As Long
    Dim r As Long
    Set WF = WorksheetFunction
    ReDim Preserve dSquared(1 To Rng1.Rows.Count)
    For r = LBound(dSquared) To UBound(dSquared)
        dSquared(r) = (WF.Rank(Rng1.Cells(r, 1), Rng1) - WF.Rank(Rng2.Cells(r, 1), Rng2)) ^ 2
    Next r
    Spearman = 1 - ((6 * WF.Sum(dSquared)) / ((Rng1.Rows.Count ^ 3) - Rng1.Rows.Count))
End Function

I tested it with the data here:

http://www.rgs.org/NR/rdonlyres/484...3C28FAC087/0/OASpearmansRankExcelGuidePDF.pdf

 

[edwinsampang]

Thanks, but why is it that when I used your UDF (see example below) I got different answers?

X	Y	Rank X	Rank Y	d^2
1	1	10	9.5	0.25
2	1	9	9.5	0.25
3	2	8	7	1
4	2	7	7	0
5	2	6	7	1
6	3	5	4	1
7	3	4	4	0
8	3	3	4	1
9	4	2	1.5	0.25
10	4	1	1.5	0.25
				5
	Spearman Rho		0.966667
SpearmanRho(User Defined Function)	0.927273

<colgroup><col style="width: 48pt;" span="5" width="64"> <tbody>
</tbody>

 

[Andrew Poulsom]

Thanks, but why is it that when I used your UDF (see example below) I got different answers?

My function doesn't average tied ranks.

 

[edwinsampang]

I think it is a requirement for Spearman's Rank Correlation coeficient that tied ranks must be averaged.

 

[Andrew Poulsom]

Can't you amend the function to cater for that?

 

[edwinsampang]

I don't think so, because Excel doesn't average tied ranks. I also wonder why the Excel developers does not amend the User defined function for ranking.

 

[Aladin Akyurek]

Is there a VBA code for the Spearman's Rank Correlation Coefficient, where its syntax is
=Spearman(Range1, Range2)?

See also:

http://www.mrexcel.com/forum/excel-questions/44080-spearmans-rank.html