Hello, I have a question regarding voids. Kendall's rate is normally calculated when all cells are filled. But, as soon as there is a gap, it displays an error. And in my database, there are columns that are filtered, which means that we have blanks in certain columns. Is there an additional line of code that could work around this problem?
| 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 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((CDbl(nC2 - nX) * CDbl(nC2 - nY))) End Function |
