Hello, I used the code that works fine. But there is a small problem, as soon as I exceed 412 lines, I get a calculation error. my database is 2200 lines. can i have help
Option Explicit
' Example data from
' Kendall's Rank Correlation - StatsDirect
' --A--- --B-- C --D--- -----------E-----------
' 1 Career Psych
' 2 4 5 0.5111 D2: =KendallTau(A2:B11)
' 3 10 8
' 4 3 6
' 5 1 2
' 6 9 10
' 7 2 3
' 8 6 9
' 9 7 4
' 10 8 7
' 11 5 1
Option Explicit
' Example data from
' Kendall's Rank Correlation - StatsDirect
' --A--- --B-- C --D--- -----------E-----------
' 1 Career Psych
' 2 4 5 0.5111 D2: =KendallTau(A2:B11)
' 3 10 8
' 4 3 6
' 5 1 2
' 6 9 10
' 7 2 3
' 8 6 9
' 9 7 4
' 10 8 7
' 11 5 1
VBA Code:
Function KendallTau(r As Range) As Variant
If WorksheetFunction.Count(r.Value) <> r.Count Then
KendallTau = CVErr(xlErrValue)
Else
KendallTau = dKendallTau(r.Value)
End If
End Function
Function dKendallTau(avXY As Variant) As Double
' shg 2010
Dim n As Long ' number of pairs
Dim i As Long ' outer loop index
Dim j As Long ' inner loop index
Dim nCon As Long ' number of concordant pairs
Dim nDis As Long ' dumber of discordant pairs
Dim nX As Long ' number of X ties
Dim nY As Long ' number of Y ties
Dim nC2 As Long ' n Choose 2
n = UBound(avXY)
For i = 1 To n - 1
For j = i + 1 To n
Select Case Choose(Sgn(avXY(i, 1) - avXY(j, 1)) + 2, "<", "=", ">") & _
Choose(Sgn(avXY(i, 2) - avXY(j, 2)) + 2, "<", "=", ">")
Case ">>", "<<"
nCon = nCon + 1
Case "<>", "><"
nDis = nDis + 1
Case "=<", "=>"
nX = nX + 1
Case "<=", ">="
nY = nY + 1
Case "=="
nX = nX + 1
nY = nY + 1
End Select
Next j
Next i
nC2 = n * (n - 1) / 2
dKendallTau = (nCon - nDis) / Sqr(((nC2 - nX) * (nC2 - nY)))
End Function
Last edited by a moderator:
