How to calculate correlation between two arrays?

[datalyst]

Hello,
I am wiritng a macro that will calculate correlation based on user input which determines how many items should be used from the two arrays to calculate the correlation.

e.g.
data starts from row 2 in excel sheet
Column A Column B
100 cells with data 100 cells with data


Array 1 Array 2
data from Column A data from column B


User input: e.g. 13 (thorugh an input box prompt)

correlation between first 13 items from Array A and Array B--> stored in cell 14( as data starts from row 2 in excel sheet)

then correlation between next 13 and stored in cell 27
.
.
.

and so on until last set of data items and stored in last row with data which is 100th row of data or 101st row in excel sheet.

any idea on how to achieve this?
Any help will be greatly appreciated.

Sincere Thanks.


user enters

 

[AlphaFrog]

Sub Correlation()

    Dim i As Long, r As Long
    
    r = Abs(Application.InputBox("Enter the number of rows to correlate.", _
                                 Title:="Number of Rows?", Type:=1))
    If r = 0 Then Exit Sub  ' User canceled
    If r > 100 Then r = 100 ' Max number of rows = 100
    
    Range("C2:C101").ClearContents  ' Clear old correlations
    
    Range("C" & r + 1).FormulaR1C1 = "=CORREL(R[-" & r - 1 & "]C[-2]:RC[-2],R[-" & r - 1 & "]C[-1]:RC[-1])"
    If r < 51 Then Range("C2:C" & r + 1).AutoFill Destination:=Range("C2:C101")
    
'    For i = r + 1 To 101 Step r
'        Range("C" & i).FormulaR1C1 = "=CORREL(R[-" & r - 1 & "]C[-2]:RC[-2],R[-" & r - 1 & "]C[-1]:RC[-1])"
'    Next i
    
End Sub