Automating Calculation of Lagged Cross Correlations between Variables

[kadghar]

Hi,

I did a search for empty cells in my range but couldn't find any. Viewing the window show only the Lag expression having a value of 0. All other numerical expressions do not have any zeros. The correlation expression has the following value of -1#NF.

Lucas

are you sure?

the only thing that comes to my mid is that you have really huge matrices, so a1 and a2 are huge huge ranges, and then i takes the value of zero... so:

Correlation = ((i * Sxy) - (Sx * Sy)) / (((i * Sx2 - Sx ^ 2) ^ 0.5) * ((i * Sy2 - Sy ^ 2) ^ 0.5))

when i = 0 then
Correlation =

-Sx*Sy / (Sx2 * Sy2)

and if you say that Sx2 is not zero and Sy2 is not zero, then i just cant imagine how the division is by zero... only if Sx2 and Sy2 are too small, that their product is smaller than 1x10^-390 (then the number is so small that it'll be taken as a zero)

but... are they that small?, is like the joke (a really bad joke) :

2 + 2 = 5, for very big values of 2.

But it can also be that i takes a huge huge value... then, i dont know what to do
perhaps we need an arbitrary precision algorithm

Anyway, why dont you post the Sx, Sy, Sx1, Sx2 and i values when the error ocurs? so we can find out where the problem is.

[kadghar]

Hi,

I did a search for empty cells in my range but couldn't find any. Viewing the window show only the Lag expression having a value of 0. All other numerical expressions do not have any zeros. The correlation expression has the following value of -1#NF.

Lucas

are you sure?

the only thing that comes to my mid is that you have really huge matrices, so a1 and a2 are huge huge ranges, and then i takes the value of zero... so:

Correlation = ((i * Sxy) - (Sx * Sy)) / (((i * Sx2 - Sx ^ 2) ^ 0.5) * ((i * Sy2 - Sy ^ 2) ^ 0.5))

when i = 0 then
Correlation =

-Sx*Sy / (Sx2 * Sy2)

and if you say that Sx2 is not zero and Sy2 is not zero, then i just cant imagine how the division is by zero... only if Sx2 and Sy2 are too small, that their product is smaller than 1x10^-390 (then the number is so small that it'll be taken as a zero)

but... are they that small?, is like the joke (a really bad joke) :

2 + 2 = 5, for very big values of 2.

But it can also be that i takes a huge huge value... then, i dont know what to do
perhaps we need an arbitrary precision algorithm

Anyway, why dont you post the Sx, Sy, Sx1, Sx2 and i values when the error ocurs? so we can find out where the problem is.

[LucasLondon]

OK, I've investigated the raw data further and seemed to have indentify the problem, at least the code is running now. One column of data did not vary, i.e it had the same values (of 11) in every row. I deleted this column and the code ran!

So it looks like that's is what was causing the error. Does that make sense from a programing perspective?

Thanks for your help.

Lucas

[kadghar]

OK, I've investigated the raw data further and seemed to have indentify the problem, at least the code is running now. One column of data did not vary, i.e it had the same values (of 11) in every row. I deleted this column and the code ran!

So it looks like that's is what was causing the error. Does that make sense from a programing perspective?

Thanks for your help.

Lucas

From a programing perspective?? I dont think so.

Well, i have to admit im a little bit rusted, but i'd say it's because of the correlation properties.

[MoeG]

No output

Recently found the site and kadghar's seemed like an excellent solution to the same problem I am facing. However, though the module runs, I receive a blank output sheet in Excel. What could be going wrong?

Thanks for everyone's patience.