Find correlations with correlation coefficient > x in massive dataset based on CORREL loop

[BenGryllls]

Hello. Im new to excell/vba so i rly have no idea if this is even possible.

Basicly im trying to do a forecastingmoddel based on patternrecognition. I wanna find high correlations of a timeseries then calculate the mean and stdev of the forecast then testing the results. My first problem is looping the correlation formula and writing the values for the location of the correlation. Any help with this would be highly apriciated.


I have 2 datasets with >50k rows called DATA 1, DATA 2. For each value in DATA2 i wanna find all correlations above a certain value in DATA 1 like this:

CORREL A1:10 in DATA 1 with DATA 2 A1-A10, then with DATA2 A2-A11, Then with DATA2 A3-A12 ...... then with DATA2 A50000-A50010
Print the rown number for all correlations above x in DATA2 A2, A3, A4 etc..

Then do the whole process over again for DATA1 A2-A11..

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

We don't need zillions of rows, just sufficient to be truly representative