multiple correlations with one click?
in [Functions]
|
Prev: EOMonth function
Next: HLOOKUP into another xls file
From: bondjel on 22 Mar 2010 12:00 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.
From: Otto Moehrbach on 22 Mar 2010 12:44 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" <bondjel(a)discussions.microsoft.com> wrote in message news:BAE161B4-D942-40B1-8FCB-F71CBEEEE977(a)microsoft.com... > 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.
From: bondjel on 22 Mar 2010 14:29 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" <bondjel(a)discussions.microsoft.com> wrote in message > news:BAE161B4-D942-40B1-8FCB-F71CBEEEE977(a)microsoft.com... > > 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. > > . >
From: Dana DeLouis on 22 Mar 2010 15:03 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 >> >> "bondjel"<bondjel(a)discussions.microsoft.com> wrote in message >> news:BAE161B4-D942-40B1-8FCB-F71CBEEEE977(a)microsoft.com... >>> 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
From: Mike Middleton on 22 Mar 2010 15:17
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" <bondjel(a)discussions.microsoft.com> wrote in message news:BAE161B4-D942-40B1-8FCB-F71CBEEEE977(a)microsoft.com... 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. |