Correlation between rows of a table

[Duncan EMmerson]

I need to understand how sales data in different rows in
one table correlate to each other. The data is in the
following format:

week1 week2 week3 etc
Outlet 1 a b c
Outlet 2 d e f
outlet 3 h i j

I need to know whether sales in Outlet 1 correlates to
that in outlet 2 or 3 etc

I would like to create another table with the correlation
values in it like this:

outlet 1 outlet 2 outlet 3
Outlet 1 1 0.6 0.7
Outlet 2 0.6 1 0.3
Outlet 3 0.7 0.3 1

I think i must be able to do some kind of index match to
bring back the row of sales data into the correlate
function.

Any ideas? This could save me days work if it works!

 

[Aladin Akyurek]

Let A14 house the following sample...

{"","Week1","Week2","Week3";"Outlet1",51,75,80;"Outlet2",34,67,39;"Outlet3",
53,34,80}

where "" stands for an empty cell.

In G1:I1 enter: {"Outlet1","Outlet2","Outlet3"}

In F2:F4 enter: {"Outlet1";"Outlet2";"Outlet3"}

In G2 enter and copy across then down:

=IF(F2=G1,1,CORREL(INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),INDEX($B$2:$D$4
,MATCH(G$1,$A$2:$A$4,0),0)))

Note that the formula recomputes the correlations below the diagonal anew,
which I didn't seek to avoid.

 

[Michael R Middleton]

Duncan Emmerson -

I need to understand how sales data in different rows in one table

correlate to each other. <

Use the Correlation tool in the Analysis ToolPak: Tools | Data Analysis |
Correlation.

- Mike Middleton, www.usfca.edu/~middleton

P.S. If the Data Analysis command doesn't appear on the Tools menu, choose
the Add-Ins command from the Tools menu; in the Add-Ins Available list box,
check the box next to Analysis Tools. If Analysis Tools doesn't appear in
the Add-Ins Available list box, you may need to add the Analysis ToolPak
through a custom installation using the Microsoft Excel Setup program.