Is it possible to add a check of data validity in the range r?

[Juggler_IN]

I need help on a UDF shared by [MENTION]shg[/MENTION]

https://www.mrexcel.com/forum/excel-questions/499657-kendall-tau-test.html

Is it possible to add a check of data validity in the range r. For example, if the data is:

[TABLE="class: cms_table, width: 128"]
<tbody>[TR]
[TD][X][/TD]
[TD][Y][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD].[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]92[/TD]
[/TR]
</tbody>[/TABLE]


Currently function fails.

Can the code be modified to ignore the 3rd pair (and any other subsequent such pairs) and compute with the remaining 4 pairs?

 

[Rijnsent]

Hi Juggler,
what I would do is first clean the input data and than feed it into the function. Something like this:

Function KendallTau(r As Range) As Double
    
    Dim cleanr() As Variant
    
    rwn = 1
    For rw = 1 To r.Rows.Count
        If IsNumeric(r(rw, 1).Value) And IsNumeric(r(rw, 2).Value) Then
            ReDim Preserve cleanr(1 To 2, 1 To rwn)
            cleanr(1, rwn) = r(rw, 1).Value
            cleanr(2, rwn) = r(rw, 2).Value
            rwn = rwn + 1
        End If
    Next rw
    cleanr1 = Application.Transpose(cleanr)
    
    KendallTau = dKendallTau(cleanr1)

End Function

Cheers,
Koen

 

[Juggler_IN]

Perfect. This does the job.

Thanks @Koen;