Macro for correlation matrix with varying data length

Stebaker

New Member
Joined
Mar 7, 2011
Messages
6
Hi All,

I'm trying to create a macro to calculate a correlation matrix of data series.

I have a worksheet called "All" with 17 columns of data, each column is a data series of numbers.

I would like to create a 17 x 17 correlation matrix in another sheet for the multiple data series using the Pearson formula.

Depending on a user input "TimePeriod" (a number from 1-7), the number of rows of data in "All", used for the calculation, varies.

So far I have:

Code:
Dim TimePeriod, rowcount, colcount, EndRow As Integer
Dim myrange1, myrange2 as Range

TimePeriod = Worksheets("Corr").Cells(9, "B").Value

End_Row = IIf(TimePeriod = 1, 13, IIf(TimePeriod = 2, 25, IIf(TimePeriod = 3, 69, IIf(TimePeriod = 4, 137, _
        IIf(TimePeriod = 5, 247, IIf(TimePeriod = 6, 507, 1005))))))


' I would now like to cycle through the 17x17 cell range and calculate the correlation values in a macro, I guess using a couple of "for" statements.

With Range("E27:G29")
    
    For rowcount = 1 to 17
        For colcount = 1 to 17

            myrange1 = [rowcount][1]:[rowcount][EndRow] ' incorrect code but I require help (at least) here
            myrange2 = [colcount][1]:[colcount][EndRow]   ' and here
                .Value = Application.WorksheetFunction.Pearson(myrange1, MyRange2)
        Next colcount
    Next rowcount
End With 
End Sub
My problem stems from the use of syntax for the range in the Pearson parameters, and entering a letter to represent the column during the loops. Can I use a numerical reference to the columns or can I loop through the Column letters?

Could any of you guys possibly help?

Thanks very much

Steve
 
Last edited:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
On what sheet does the data appear, starting in what cell?

Do the 13, 25, 69 ... values mark the number of rows of data or the last row of data?

On what sheet should the output appear, starting in what cell?

You want to calculate the correlation of each column of the data with each other column?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
This is an example of the syntax you can use to define a range of cells using integers. I don't follow exactly what range you want to define.
Code:
            myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Code:
 myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))

In the code, myrange1 is a Variant and myrange2 is a Range. That line would return the values of the first range to the Variant (which would be fine), but the next line would give a run-time error.
 

Stebaker

New Member
Joined
Mar 7, 2011
Messages
6

ADVERTISEMENT

Hi,

Thanks for quick responses....

shg4421

  • data should appear E27:G29 on my active sheet (called "Corr")
  • data series appears in sheet called "All", range B2:Rx
  • x = EndRow defined by user (13, 25,...)
  • Yes, a standard correlation matrix with 1's on the top left to bottom right diagonal
AlphaFrog

Thanks for the line of code, I have tried this below, but there is an error with

Code:
 myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))
Code:
Sub CorrMatrix()
'
'

'
    
Dim TimePeriod, rowcount, colcount, EndRow As Integer
Dim myrange1, myrange2 As Range

TimePeriod = Worksheets("Corr").Cells(9, "B").Value

End_Row = IIf(TimePeriod = 1, 13, IIf(TimePeriod = 2, 25, IIf(TimePeriod = 3, 69, IIf(TimePeriod = 4, 137, _
        IIf(TimePeriod = 5, 247, IIf(TimePeriod = 6, 507, 1005))))))


' I would now like to cycle through the 17x17 cell range and calculate the correlation values in a macro, I guess using a couple of "for" statements.

With Worksheets("Corr").Range("E27:G29")
    
    For rowcount = 1 To 17
        For colcount = 1 To 17
        
            myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))
            myrange2 = Range(Worksheets("All").Cells(1, colcount), Worksheets("All").Cells(EndRow, colcount))
            .Value = Application.WorksheetFunction.Pearson(myrange1, myrange2)
            
        Next colcount
    Next rowcount
                
End With

End Sub
Cheers Guys!

Steve
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Try this:
Code:
Sub x()
    Dim iPer        As Long
    Dim lRow        As Long
    Dim rInp        As Range
    Dim iCol1       As Long
    Dim iCol2       As Long
 
    iPer = Worksheets("Corr").Range("B9").Value
    If iPer < 1 Or iPer > 7 Then Exit Sub
    
    lRow = Choose(iPer, 13, 25, 69, 137, 247, 507, 1005)
    With Worksheets("All")
        Set rInp = .Range("B2", .Cells(lRow, "R"))
    End With
 
    With Worksheets("Corr").Range("E27")
        For iCol1 = 1 To 17
            For iCol2 = 1 To 17
                .Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
                                             rInp.Columns(iCol1).Value, _
                                             rInp.Columns(iCol2).Value)
            Next iCol2
        Next iCol1
    End With
End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400

ADVERTISEMENT

Need the Set keyword. Sorry.
Code:
[COLOR="Red"]Set [/COLOR]myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))

Or this...
Code:
With Worksheets("All")
   [COLOR="Red"]Set[/COLOR] myrange1 = .Range(.Cells(1, rowcount), .Cells(EndRow, rowcount))
End With
 

Stebaker

New Member
Joined
Mar 7, 2011
Messages
6
Thanks again for replies...

shg4421 your code works great, apart from when the user input = 1 there is a time out code 1004 on the line:

Code:
.Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
                                             rInp.Columns(iCol1).Value, _
                                             rInp.Columns(iCol2).Value)

Seems a strange error, and can't understand why it occurs???¿¿¿ Otherwise perfect, thanks a lot! :biggrin:

Cheers

Steve
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
That's curious -- 1 is the only value I tested with, and it worked fine.

All the values in the input range must be numbers.
 

Stebaker

New Member
Joined
Mar 7, 2011
Messages
6
Resolved... first 7 rows of series data missing in "All" - empty rows deleted and this now works perfectly as per your testing!

Thanks again for your help, awesome...:)

S
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top