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?
[TABLE="width: 240"]
<colgroup><col style="width: 48pt;" span="5" width="64"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]X[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Rank X[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Rank Y[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]d^2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]9.5[/TD]
[TD="class: xl65, bgcolor: transparent"]0.25[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]9[/TD]
[TD="class: xl65, bgcolor: transparent"]9.5[/TD]
[TD="class: xl65, bgcolor: transparent"]0.25[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]9[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]1.5[/TD]
[TD="class: xl65, bgcolor: transparent"]0.25[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]1.5[/TD]
[TD="class: xl65, bgcolor: transparent"]0.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Spearman Rho[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.966667[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]SpearmanRho(User Defined Function)[/TD]
[TD="bgcolor: transparent, align: right"]0.927273[/TD]
[/TR]
</tbody>[/TABLE]

 

[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