PDA

View Full Version : Creating a lagged correlation matrix



hockeyfan
05-05-2008, 05:44 PM
I have a matrix of numbers in A2:C10 on one work sheet.
In another I want to do a correlation matrix, that will look like this

1 2 3
4 5 6
7 8 9

The cells that will hold the correlations are B2:d4


1. =Correl("A3:A10","A2:A9") -Is A a lagging indicator of A?
2. =Correl("A3:A10","B2:B9") -Is A a lagging indicator of B?
3. =Correl("A3:A10","C2:C9") -Is A a lagging indicator of C?
4. =Correl("B3:B10","A2:A9") -Is B a lagging indicator of A?
5. =Correl("B3:B10","B2:B9") -Is B a lagging indicator of B?
6. =Correl("B3:B10","C2:C9") -Is B a lagging indicator of C?
7. =Correl("C3:C10","A2:A9") -Is C a lagging indicator of A?
8. =Correl("C3:C10","B2:B9") -Is C a lagging indicator of B?
9. =Correl("C3:C10","C2:C9") -Is C a lagging indicator of C?


I hope I explained that OK... So basically I am having trouble doing this... Its a nightmare to do with loops and R1C1 formulas, does anyone have any good ideas?

hockeyfan
05-06-2008, 01:34 PM
bump?

mdmackillop
05-06-2008, 02:19 PM
Search "lagged" to get a couple of previous questions. If you still need help, let us know. I don't know the use of this function myself.

hockeyfan
05-06-2008, 02:24 PM
K thx. Let me ask you this, in the meantime I did try to get it to work using R1C1 formulas. I get Application-defined or object-defined error though...cant see where the problem is though:

Worksheets("Correlation Matrix").Cells(1 + x, 1 + i).FormulaR1C1 = _
"=CORREL(Data!R[" & x & "]C[-" & i & "]:R[" & Lastrow - x - 2 & "]C[-" & i & "], _
Data!R[" & x - 1 & "C[-" & i & "]:R[" & Lastrow - x - 4 & "]C[-" & i & "])"

mdmackillop
05-06-2008, 02:34 PM
Can you post your workbook?

mdmackillop
05-06-2008, 02:45 PM
Run this

Sub test()
lastrow = 5
x = 6
i = 7
Worksheets("Correlation Matrix").Cells(1 + x, 1 + i).FormulaR1C1 = _
"CORREL(Data!R[" & x & "]C[-" & i & "]:R[" & _
lastrow - x - 2 & "]C[-" & i & "], Data!R[" & x - 1 & "C[-" & i & "]:R[" _
& lastrow - x - 4 & "]C[-" & i & "])"
End Sub



to get
CORREL(Data!R[6]C[-7]:R[-3]C[-7], Data!R[5C[-7]:R[-5]C[-7])

There is an error at R[5C[