| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correlation matrix
Many thanks! The hidden treasures of Excel!
"Bernie Deitrick" wrote: 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 "Chrissy Wissy" wrote in message ... 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" wrote: 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 "Chrissy Wissy" wrote in message ... 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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| triangular correlation matrix | Excel Discussion (Misc queries) | |||
| Toolpack Correlation Matrix | Excel Worksheet Functions | |||
| Correlation Matrix | Excel Worksheet Functions | |||
| CORRELATION / COVARIANCE MATRIX | Excel Worksheet Functions | |||
| Correlation matrix | Excel Worksheet Functions | |||
![Chrissy Wissy[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
