Dynamic Correlation (possible PivotTable)

[CJG5667]

I am trying to figure out a way to do dynamic correlation between variables by choosing start and end date, (Vertical Column A), and doing correlation between 2 different columns of values. The kicker is there is also a column of identifiers in Column B. So i'd like to select correlation between the identifiers in column B vs another item in column B, using the start and end dates in column A, but selecting values in C and D or C and E of C and F...ETC

I've figured out how to do this without the identifiers by using but this is missing the identifier selection.
(=CORREL(INDEX(A1:F12,MATCH(J2,A1:A12,0),MATCH(K2,A1:F1,0)):INDEX(A1:F12,MATCH(J3,A1:A12,0),MATCH(K2,A1:F1,0)),INDEX(A1:F12,MATCH(J2,A1:A12,0),MATCH(K3,A1:F1,0)):INDEX(A1:F12,MATCH(J3,A1:A12,0),MATCH(K3,A1:F1,0)))

Unfortunately the program I use gives data in this way. Id like to see the correlation between A and C, for dates 2015-2016, and values 1 and 3 for instance. I realize there has to be an easier way to do this. I've only used pivots briefly but realize there may be some utility there.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Identifier[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[/TR]
[TR]
[TD]12/31/15[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/31/16[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12/31/17[/TD]
[TD]C[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]12/31/15[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/31/16[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12/31/17[/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi CJG,
I guess a Pivot is indeed a good first step. I'm assuming your data has a bunch of dates and only one row for a given date-identifier combination?
Step by step (i'm translating my NL version of Excel 365, so hope the button names match my description):
-select your data range and click "create pivot"
-drag the Identifier to "columns", Date to "rows" and e.g. Value 1 to "Values". Note: with date it might aggregate to years/quarters in the newer Excel versions, if so: right click on a cell and click "remove grouping".
-on the pivot, select the "design" menu and "subtotals"->off, "End totals"->off for rows and columns, Report design->"Table view" and Report design->repeat all item labels.
That should be the basis on which it's rather easy to do a CORREL function, it basically prepared the data for you.

Hope that helps,

Koen