1

Hello I am trying to find the correlations between variables in excel. My data is structured as follows across different sheets,

Location Variable Year 1 Year 2 Year 3
Place A Indicator 1 1 2 3
Place A Indicator 2 5 7 9
Place A Indicator 3 10 4 3
Place B Indicator 1 25 17 10
Place B Indicator 2 11 62 43
Place B Indicator 3 5 7 8

Using only excel how do you find the correlation between indicators across all locations, as the built in correlation matrix would only compare the indicators of a single location.

Thank you

howkesh
  • 11
  • 3
  • Could you provide the table you need to display in Excel? You can try to create the sample result here... – Lee Apr 11 '22 at 09:37

1 Answers1

0

I am assuming you are currently using the correlation "wizard" as part of the analysis toolpak as described e.g. here by Madhuri Thakur from EDUCBA. The wizard output the correlation matrix for a given input range.

Using this method, you would need to select each place's range of data individually and run the wizard as many times as you have places - not very practical!

Instead, you can use the CORREL function to calculate for each place, the indivual elements of the correlation matrix. Since your list is sorted and you seem to always have the same number of indicators, you can use put the follow three formula below each other

=CORREL(C2:E2, C3:E3)
=CORREL(C2:E2, C4:E4)
=CORREL(C4:E4, C3:E3)

The select all three and drag down via the fill handle.

If you want to correlate place A indicator 1 with place B indicator 1, first sort your table by the indicator column, then do a similar formulation. Keep in mind the number of cells required is nC2 (as in combination), so you might have to split your CORREL functions over more columns.

ExcelEverything
  • 3,114
  • 1
  • 4
  • 16
  • Alternatively you can also generate your own matrix on a separate sheet. Use MATCH to find each row and each column from the matrix in the source date, which returns a row index to use with INDEX in the next step. Then use `CORREL(INDEX(...):INDEX(...), INDEX(...):INDEX(...))` with INDEX:INDEX returning the whole range for each Xi to construct the range for each indicator to correlate. If you have 365, the LET function would be useful to store the match indexes in variables, as you'll use them several times. – ExcelEverything Apr 13 '22 at 10:16
  • Ps. You might new to create a helper column with a unique "key" for each data set by combining place and indicator e.g. `=A2&";"&B2` - this way you can find the row index for any "place;indicator" combo in your table easily using MATCH. – ExcelEverything Apr 13 '22 at 10:20