CODEWITHSUNDEEP
excelmatchvlookup
Maddy
Maddy

Reputation: 2570

Calculate correlation in Excel after matching on a column

I need to calculate correlations for multiple years. The trick is in selecting appropriate rows from 2 columns after matching values a third column.

enter image description here

Upvotes: 0

Views: 946

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

Use the CORREL() formula as an array formula:

=CORREL(IF($A$2:$A$12 = E2,$B$2:$B$12),IF($A$2:$A$12 = E2,$C$2:$C$12))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 3

Related Questions