• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Correlation Matrices in Excel

Is there an easy way to get Excel to fill in the upper half of a correlation matrix? I realize that it mirrors the lower half which is why it is left blank, but in order to complete the calculations I am doing I need the entire matrix. I am tired of copying and pasting (transposed).
Thanks
0
eangrist
Asked:
eangrist
  • 4
1 Solution
 
calacucciaCommented:
Hi eanqrist,

Place the cursor on the left top cell of your macro and run following macro from there. It will perform the copy and paste you wish for any matrix dimension

Sub FillMatrix()
a = Selection.Row
Selection.End(xlDown).Select
b = Selection.Row
n = b - a + 1
ActiveCell.Offset(1 - n, 0).Select
For i = 1 To n
For j = i + 1 To n
Selection.Offset(i - 1, j - 1).Value = Selection.Offset(j - 1, i - 1).Value
Next j
Next i
End Sub



Hope this helps,
Geert
0
 
calacucciaCommented:
Sorry, I wrote

>Place the cursor on the left top cell of your macro and run following....

I meaned to write:

Place the cursor on the left top cell of your matrix and run following....

Bye

0
 
calacucciaCommented:
########################################################################################################################

Just to look if by doing this I could manipulate the width of the thread
0
 
calacucciaCommented:

Yep, it worked, sorry for that
0
 
eangristAuthor Commented:
Thanks very much!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now