Calculation of correlation based on different data rows

[datalyst]

i have this set of data where user defines the start row for data(Column A) and last row for data. based on that we do some calculations and store results in column e, f and g. The column H contains the correlation between F and G. The user defines how many rows (say n)should we consider for correlation and then that is stored in column H. the next cell after this most recent cell contains the next n rows.


e.g.
Row 1 contains labels.
start row =2
end row =121( may have more rows than 121)

user enters "6" for considering rows at a time through a prompt which takes Row F2 to Row F7 and G2 to G7, the result goes into H7, next F3:F8 and G3 to G8 goes to H8 and so on. Its the concept of Simple Moving average but we use correaltion formula. i am not able to loop through the rows defined for correlation.

 

[hockey1859]

Try this code. It should put your Correl formula in Column H referencing the correct ranges.

Hope this helps.

Sub Correlation()

GivenStartRow = InputBox("Start Row")
EndRow = InputBox("End Row")
i = InputBox("How many rows at a time?") - 1
TrueStart = WorksheetFunction.Sum(GivenStartRow, i)
Range("H" & TrueStart).Select
For ThisRow = TrueStart To EndRow

ActiveCell.Formula = "=Correl(F" & ActiveCell.Offset(-i, 0).Row & ":F" & ActiveCell.Row & ",G" & ActiveCell.Offset(-i, 0).Row & ":G" & ActiveCell.Row & ")"
ActiveCell.Offset(1, 0).Select
Next ThisRow

End Sub