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

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,194