Correlation matrix

[Guest]

Hi
this is a point I have also raised in the graphs section. I was wondering if
there was an excel addin that allows a coorelation matrix to be ploteed as a
contour plot. What I want to essentially view is a contour plot showing the
"intensity" (r
= 0 - 1) of the correlations between each variable for all combinations. This
is primarily because I have complex data and such a realisation would greatly
enhance my interpretation. For example, the "diagonal" in the contour plot
would show a maximum intensity of 1 for each combination of x1/x1, x2/x2,
etc. It would be quite straightforward to place the mirror image of the
matrix data above the triangular matrix should that be required by the
application.

 

[Bernie Deitrick]

Chrissy,

You cannot have a meaningful correlation value for less than three sets of data points. Are there
more points that go into it?

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry, I should have said three _pairs_ of data points, not _sets_. In other words, you must have
two equal-sized sets, each with at least three points.

Bernie
MS Excel MVP

 

[Guest]

Hi,
I should have made myself clearer. The correlations are based on 16
measurements for each variable. I want to plot every correlation coefficient
(for all variable combinations) as an intensity contour. It is quite hard to
find an application that can plot a correlation matrix in its normal form.
The only way with Excel in its bare form as I see it is to convert the matrix
into a very long list of x (variable), y (variable), z (correlation
coefficient) values!

 

[Bernie Deitrick]

Chrissy,

You can do it easily enough. Put your lists of sixteen variables in colums
A to P starting in row 17, with one set per row. Then copy those sets and
transpose them to Q1. (If you have different sets, enter them in columns
starting in cell Q1, to Q16, then R, S, etc.)

Then in cell Q17, enter the formula

=CORREL($A17:$P17,Q$1:Q$16)

and then copy that formula into the block that matches your filled rows and
columns. That will create your matrix of values, that you can then plot.

HTH,
Bernie
MS Excel MVP

 

[Mike Middleton]

Chrissy Wissy -

You wrote: > It is quite hard to find an application that can plot a
correlation matrix in its normal form. The only way with Excel in its bare
form as I see it is to convert the matrix into a very long list of x
(variable), y (variable), z (correlation coefficient) values! <

No, it's not hard. Excel's 3-D Column chart type and Surface chart type use
worksheet data in what you have called "normal form."

That is, X values across the top, Y values down the left side, and Z values
in the body of the table.

If the data is in list (database) form, i.e., a row for each x, y, and z
value, you can use the Pivot Table feature to put it into a table for
charting.

Jon Peltier has an article at
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=447

- Mike

www.MikeMiddleton.com

 

[Guest]

Many thanks!

 

[Guest]

Many thanks! The hidden treasures of Excel!

Chrissy,

You can do it easily enough. Put your lists of sixteen variables in colums
A to P starting in row 17, with one set per row. Then copy those sets and
transpose them to Q1. (If you have different sets, enter them in columns
starting in cell Q1, to Q16, then R, S, etc.)

Then in cell Q17, enter the formula

=CORREL($A17:$P17,Q$1:Q$16)

and then copy that formula into the block that matches your filled rows and
columns. That will create your matrix of values, that you can then plot.

HTH,
Bernie
MS Excel MVP