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

Function SpearmanAvg(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_Avg(Rng1.Cells(r, 1), Rng1) - WF.Rank_Avg(Rng2.Cells(r, 1), Rng2)) ^ 2

Next r

SpearmanAvg = 1 - ((6 * WF.Sum(dSquared)) / ((Rng1.Rows.Count ^ 3) - Rng1.Rows.Count))

End Function

Function SpearmanEq(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_Eq(Rng1.Cells(r, 1), Rng1) - WF.Rank_Eq(Rng2.Cells(r, 1), Rng2)) ^ 2

Next r

SpearmanEq = 1 - ((6 * WF.Sum(dSquared)) / ((Rng1.Rows.Count ^ 3) - Rng1.Rows.Count))

End Function