Conditional Correlation?

Question

Given: I have columns something like

| Location  | Year | A | B   |
|-----------|------|---|-----|
| Delhi     | 1980 | 4 | 3.4 |
| Mumbai    | 1986 | 3 | 3.9 |
| Delhi     | 1990 | 5 | 4.4 |
| Bangalore | 1997 | 2 | 2.6 |
| Delhi     | 1998 | 4 | 3.8 |
| Delhi     | 1991 | 4 | 4.5 |
| Bangalore | 1987 | 4 | 3.8 |
| Mumbai    | 1998 | 5 | 4.8 |

And I want to perform correlation between column A and B under Delhi Location category.

I want to perform correlation with only Delhi as Location

| Location | A | B   |
|----------|---|-----|
| Delhi    | 4 | 3.4 |
| Delhi    | 5 | 4.4 |
| Delhi    | 4 | 3.8 |
| Delhi    | 4 | 4.5 |

I tried CORREL() function but this will give correlation A and B for all location. I just want specific Location to correlated.

Thank you for your time and consideration.

Answer 1

The solution is some basic array formula filtering. By making each correlation range dependent on whether A2:A9 is Delphi, you create a conditional correlation.

=CORREL(IF(A2:A9="Delhi", C2:C9), IF(A2:A9="Delhi", D2:D9))

This is an array style formula. As such, you need to uses Ctrl+Shift+Enter to finalize the formula; not just Enter. If you do this correctly, Excel will wrap the formula in maths braces; e.g. { and }.

Using this method the answer to your sample data is 0.481869424652427.