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.

Date	Identifier	Value 1	Value 2	Value 3
12/31/15	A	1	3	2
12/31/16	B	5	2	3
12/31/17	C	4	2	4
12/31/15	A	3	3	1
12/31/16	B	3	2	3
12/31/17	C	1	3	1

<tbody>
</tbody>

 

[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