If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
multiple correlations with one click?
I have a numerical data set with 11 columns and 1809 rows. I would like to
simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. |
#2
|
|||
|
|||
multiple correlations with one click?
I know what a correlation is but I have no idea what you want. Define
"correlation" in your context. IOW, what do you want to see as the result? HTH Otto "bondjel" wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. |
#3
|
|||
|
|||
multiple correlations with one click?
Think of two columns of numbers: in col 1 you have body weights and in col 2
you have no. of calories eaten per day. You want to know if those who eat a greater no. of calories per day have higher weights. You can use either PEARSON or CORREL to obtain the degree to which higher weights go with greater no. of calories eaten. Now imagine you have 11 columns of numbers and you want to do a correlation between each col and every other col. You could laboriously go through and apply PEARSON or CORREL to cols 1 and 2, then 1 and 3, then 1 and 4, and so on. I bet there's a way to tell Excel to calculate each and every 2 col correlation at one time and that's what I'm looking for. Thanks for any help. "Otto Moehrbach" wrote: I know what a correlation is but I have no idea what you want. Define "correlation" in your context. IOW, what do you want to see as the result? HTH Otto "bondjel" wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. . |
#4
|
|||
|
|||
multiple correlations with one click?
On 3/22/2010 2:29 PM, bondjel wrote:
Think of two columns of numbers: in col 1 you have body weights and in col 2 you have no. of calories eaten per day. You want to know if those who eat a greater no. of calories per day have higher weights. You can use either PEARSON or CORREL to obtain the degree to which higher weights go with greater no. of calories eaten. Now imagine you have 11 columns of numbers and you want to do a correlation between each col and every other col. You could laboriously go through and apply PEARSON or CORREL to cols 1 and 2, then 1 and 3, then 1 and 4, and so on. I bet there's a way to tell Excel to calculate each and every 2 col correlation at one time and that's what I'm looking for. Thanks for any help. "Otto Moehrbach" wrote: I know what a correlation is but I have no idea what you want. Define "correlation" in your context. IOW, what do you want to see as the result? HTH Otto wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. Hi. Would this work? This assumes your data starts in A1, and writes data to Columns 13-15 Sub Demo() Dim Tbl Dim a As Long 'First Column Dim b As Long 'Second Column Dim R 'Row R = 1 Set Tbl = [A1].Resize(1809, 11) With WorksheetFunction For a = 1 To 10 For b = a + 1 To 11 Cells(R, 13) = a Cells(R, 14) = b Cells(R, 15) = .Correl(Tbl.Columns(a), Tbl.Columns(b)) R = R + 1 Next b Next a End With End Sub = = = = = = = HTH :) Dana DeLouis |
#5
|
|||
|
|||
multiple correlations with one click?
bondjel -
The Correlation tool of the Analysis ToolPak (provided with all versions of Excel) creates a table of all pairwise correlations. That seems to be exactly what you want. - Mike http://www.MikeMiddleton.com "bondjel" wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. |
#6
|
|||
|
|||
multiple correlations with one click?
Mike,
You know, I somehow got it to do this the other day but when I tried to repeat my steps this AM I couldn't get it to do it. Can you tell me what I should highlight and what specific steps I need to to complete to get it to work? Jim "Mike Middleton" wrote: bondjel - The Correlation tool of the Analysis ToolPak (provided with all versions of Excel) creates a table of all pairwise correlations. That seems to be exactly what you want. - Mike http://www.MikeMiddleton.com "bondjel" wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. . |
#7
|
|||
|
|||
multiple correlations with one click?
I didn't realize that the "Correlation tool" which is accessed thru the
Analysis Toolpack is any different from the Correl function in regular Excel. The former does give my matrix. "Mike Middleton" wrote: bondjel - The Correlation tool of the Analysis ToolPak (provided with all versions of Excel) creates a table of all pairwise correlations. That seems to be exactly what you want. - Mike http://www.MikeMiddleton.com "bondjel" wrote in message ... I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. . |
Thread Tools | |
Display Modes | |
|
|