Estimating correlation between two consecutive arrays in a sheet..

[drniranjan]

Hi Frnds,
I have data pairs say more than 20 pairs, means there are data in 40 columns. all the pairs do have different lenghth of data sets..

I need to estimate correlation between cosecutive pairs...and write at the bottom of respective coulmn.

For example there are three pairs of data:
A B C D E F
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>1.63</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>2.38</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>1.97</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>14.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>7.81</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>599</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.52</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>1.58</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>5.21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>246</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2.82</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>53.1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.67</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>5.12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>3.33</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>88.4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>3.58</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>108</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.96</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>17.9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>10.49</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2710</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2.77</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>0.66</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>8.8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>1130</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2.47</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>37.2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">0.81</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>3.06</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>60.6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>1.59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2.42</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>1.94</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>13.4</TD></TR></TBODY></TABLE>
0.74
I need 'R' betwen A and B; C and D; E and F so on.... and to write just in the cell below the last cell of respective coulmn as shown above...

Kindly help,

Best, niru

 

[Sektor]

How did you get "0.66"?

 

[drniranjan]

Hi Sektor,
Those R values are not the true values..just to show, how I need the result, I had posted arbitrary values. Pls ignore those R values...

regards, niru

 

[Sektor]

Sub FindCorrelation()

    Dim i As Integer
    
    For i = 1 To Range("A1").End(xlToRight).Column Step 2
        Cells(1, i).End(xlDown).Offset(1, 0) = WorksheetFunction.Correl( _
            Range(Cells(1, i), Cells(1, i).End(xlDown)), _
            Range(Cells(1, i + 1), Cells(1, i + 1).End(xlDown)))
    Next

End Sub

 

[drniranjan]

thanks...it is working fine...best, niru