Conditional correlation of returns

[pathway1]

Hello forum,
Thank you in advance for your help.
I need to create a correlation matrix (simple, edited pic attached) that returns the correlation coefficient of columns 1 and 2 for the subset of periods where a third column, Equity, is down at least -2%.
Within a conditional correlation, I nested a condition of what I thought was "if downside deviation exceeds -2%", which I expected to ignore any period when US equity was >-2%, thus return correlation of columns 1 and 2 for the subset of periods that I was looking for. Instead, it is just returning the correlation of columns 1 and 2. What have I missed here, and how do I fix it (Excel only)?

My incorrect attempt (cell A3):
=CORREL(IF(STDEV(IF($L$15:$L$29<-2%,$L$15:$L$29,"")), $A$15:$A$29),$B$15:$B$29)

 

[DRSteele]

Using function FILTER is the easiest way to extract values from lists. Like this:

MrExcel posts19.xlsx
	B	C	D	E	F	G	H	I	J
3		hurdle	-2.0%		correlation:	-0.07772		correlation (single cell):	-0.07772
4
5	1	2	e		helper 1	helper 2
6	9.9%	6.6%	2.1%		-3.1%	7.8%
7	1.1%	8.7%	2.0%		7.0%	-4.9%
8	-3.1%	7.8%	-3.0%		-0.6%	-7.3%
9	-9.8%	9.3%	-1.0%		1.6%	8.6%
10	5.5%	-9.2%	5.0%		5.0%	7.6%
11	7.0%	-4.9%	-8.0%		5.2%	-2.2%
12	-7.5%	-1.9%	-1.0%		-1.7%	-7.2%
13	-1.5%	5.9%	-1.0%		-5.9%	6.5%
14	-4.5%	1.3%	6.0%		-9.5%	-1.6%
15	1.2%	5.1%	6.0%
16	-0.6%	-7.3%	-3.0%
17	-7.9%	-9.0%	3.0%
18	1.6%	8.6%	-5.0%
19	5.0%	7.6%	-8.0%
20	5.2%	-2.2%	-9.0%
21	-1.7%	-7.2%	-5.0%
22	-3.7%	7.4%	5.0%
23	-5.9%	6.5%	-10.0%
24	-9.5%	-1.6%	-3.0%
Sheet37

Cell Formulas
Range	Formula
G3	G3	=CORREL(F6#,G6#)
J3	J3	=CORREL(FILTER(B6:B24,D6:D24<=D3),FILTER(C6:C24,D6:D24<=D3))
F6:F14	F6	=FILTER(B6:B24,D6:D24<=D3)
G6:G14	G6	=FILTER(C6:C24,D6:D24<=D3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:D24	Cell Value	<=$D$3	text	NO

 

[pathway1]

Perfect, thank you so much!

 

[DRSteele]

Super! You're welcome.