CORREL function on filtered data

[Marc_D]

Hi,

I would like to know if it is possible to use the CORREL function (or any other function) on a filtered data subset. When I use a function, it always seems to analyse all the data, even when I filter out a subset.

Is there a solution ?

 

[Damon Ostrander]

Hi Marc_D,

I don't know how to make the CORREL function ignore filtered (hidden) values, but it is easy to write a user-defined function (UDF) that does ignore the filtered values. Here is an example of a UDF to replace the CORREL function:

Function Correl8(R1 As Range, R2 As Range) As Double
' correlation coefficient function that ignores hidden values
Dim Sig1 As Double
Dim Sig2 As Double
Dim S1 As Double
Dim S2 As Double
Dim Mu1 As Double
Dim Mu2 As Double
Dim N As Integer
Dim i As Integer

Sig1 = 0: Sig2 = 0: Mu1 = 0: Mu2 = 0: S1 = 0: S2 = 0
N = 0

For i = 1 To R1.Cells.Count
If Not R1.Rows(i).Hidden And Not R2.Rows(i).Hidden Then
N = N + 1
Mu1 = Mu1 + R1.Cells(i)
Mu2 = Mu2 + R2.Cells(i)
S1 = S1 + R1(i) ^ 2
S2 = S2 + R2(i) ^ 2
End If
Next i

Sig1 = Sqr((N * S1 - Mu1 ^ 2)) / N
Sig2 = Sqr((N * S2 - Mu2 ^ 2)) / N
Mu1 = Mu1 / N
Mu2 = Mu2 / N

Correl8 = 0
For i = 1 To R1.Cells.Count
If Not R1.Rows(i).Hidden And Not R2.Rows(i).Hidden Then
Correl8 = Correl8 + (R1.Cells(i) - Mu1) * (R2.Cells(i) - Mu2)
End If
Next i

Correl8 = Correl8 / Sig1 / Sig2 / N

End Function