Excluding cells from calculations based on threshold

[andrewmurray86]

Hey all!

I'm trying to find 2 values based on the data in the cells. It is a calculation between the highs and lows.
The first is the "Max Drawdown", the lowest point from the starting value (4000 or cell v1), the formula works fine here. The formula is this: =IF(R2:R200<4000,MAX(AC2:AC200)) see Cell v28
The second is the "Relative Drawdown", for which I want to find the lowest point after the highest peak. The formula for this is =IF(R2:R200>4000,MAX(AC2:AC200)) cell v29
As you can see the only difference is the >< symbols which I had hoped would be a simple exclusion for values that are below the starting point. But as you can see I'm getting "FALSE" in the target cell. The value should 5.57% per cell v94

Any suggestions to achieve my goal here. I don't understand why it's not working??

Copy of Andrew_Murray_Trade_Results_2020-1.xlsx
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Equity		Beginning Equity		4000						$4,000.00
2	$3,995.97										$4,000.00	0.10%
3	$3,991.93		Updated when trade is closed								$4,000.00	0.20%
4	$3,988.28		Realized Profit/Loss		3834.23	Profit/Loss of all closed trades					$4,000.00	0.29%
5	$3,984.64		Gross profit		669.63	Total profit of all won trades					$4,000.00	0.38%
6	$3,980.38		Gross loss		835.4	Total loss of all lost trades					$4,000.00	0.49%
7	$3,976.13		Expected payoff		-$1.76	Average trade outcome					$4,000.00	0.60%
8	$3,936.21		Profit factor		0.801568111	Gross profit / gross loss					$4,000.00	1.59%
9	$3,896.30		Number of closed trades		Per Year	57					$4,000.00	2.59%
10	$3,896.14		Profit trades			32					$4,000.00	2.60%
11	$3,895.98		Loss trades			25					$4,000.00	2.60%
12	$3,867.16		Largest profit trade			$94.11					$4,000.00	3.32%
13	$3,838.39		Largest loss trade			-$42.44					$4,000.00	4.04%
14	$3,798.00		Average profit trade			$20.90					$4,000.00	5.05%
15	$3,757.61		Average loss trade			-$24.68					$4,000.00	6.06%
16	$3,716.97		Long trades			32					$4,000.00	7.08%
17	$3,676.33		Short trades			25					$4,000.00	8.09%
18	$3,675.41		Long trades won			24					$4,000.00	8.11%
19	$3,675.09		Short trades won			8					$4,000.00
20	$3,681.66		Max. consecutive wins		6	Longest winning streak (number of trades)					$4,000.00
21	$3,688.27		Max. consecutive losses		8	Longest losing streak (number of trades)					$4,000.00
22	$3,764.01		Max. consecutive profit		198.29	Largest winning streak (total profit)					$4,000.00
23	$3,788.03		Max. consecutive loss		-219.65	Largest losing streak (total loss)					$4,000.00
24	$3,812.48		Paid commissions		0						$4,000.00
25	$3,812.78										$4,000.00	4.68%
26	$3,812.30		Win Rate		56.14%						$4,000.00
27	$3,837.65										$4,000.00
28	$3,931.76		Max Drawdown		8.11%						$4,000.00
29	$3,949.48		Relative Drawdown		FALSE						$4,000.00
30	$4,010.59										$4,010.59	0.00%
31	$4,007.91										$4,010.59
32	$4,008.19										$4,010.59
33	$4,030.97										$4,030.97
34	$4,031.25										$4,031.25
35	$4,053.77										$4,053.77	0.00%
36	$4,053.31										$4,053.77	0.01%
37	$4,053.07										$4,053.77	0.02%
38	$4,052.70										$4,053.77	0.03%
39	$4,052.45										$4,053.77	0.03%
40	$4,017.59										$4,053.77	0.89%
41	$3,982.72										$4,053.77	1.75%
42	$3,982.09		$3,939.51								$4,053.77
43	$3,999.01										$4,053.77
44	$4,015.93										$4,053.77
45	$4,032.77										$4,053.77
46	$4,049.60										$4,053.77	0.10%
47	$4,048.68										$4,053.77	0.13%
48	$4,048.31										$4,053.77	0.13%
49	$3,948.31										$4,053.77	2.60%
50	$3,928.31										$4,053.77	3.09%
51	$3,928.14										$4,053.77
52	$3,928.96										$4,053.77
53	$3,951.16										$4,053.77
54	$3,976.09										$4,053.77
55	$3,976.97										$4,053.77	1.89%
56	$3,960.06										$4,053.77	2.31%
57	$3,943.26										$4,053.77
58	$3,966.08										$4,053.77
59	$3,977.22										$4,053.77
60	$3,977.82										$4,053.77
61	$4,000.62										$4,053.77
62	$4,024.03										$4,053.77
63	$4,040.33										$4,053.77	0.33%
64	$4,039.76										$4,053.77
65	$4,060.98										$4,060.98
66	$4,061.35										$4,061.35	0.00%
67	$4,059.70										$4,061.35	0.04%
68	$4,059.12										$4,061.35	0.05%
69	$4,058.87										$4,061.35	0.06%
70	$4,058.61										$4,061.35	0.07%
71	$4,016.17										$4,061.35	1.11%
72	$3,973.76										$4,061.35	2.16%
73	$3,973.47										$4,061.35	2.16%
74	$3,940.03										$4,061.35	2.99%
75	$3,906.59										$4,061.35
76	$3,932.69										$4,061.35	3.17%
77	$3,932.20										$4,061.35	3.18%
78	$3,864.20										$4,061.35	4.85%
79	$3,861.67										$4,061.35
80	$3,887.37										$4,061.35	4.28%
81	$3,885.99										$4,061.35	4.32%
82	$3,884.94										$4,061.35	4.34%
83	$3,883.92										$4,061.35	4.37%
84	$3,882.93										$4,061.35	4.39%
85	$3,881.94										$4,061.35
86	$3,882.93										$4,061.35	4.39%
87	$3,879.87										$4,061.35	4.47%
88	$3,879.08										$4,061.35	4.49%
89	$3,878.04										$4,061.35	4.51%
90	$3,877.05										$4,061.35	4.54%
91	$3,876.22										$4,061.35	4.56%
92	$3,857.20										$4,061.35	5.03%
93	$3,838.17										$4,061.35	5.50%
94	$3,835.12										$4,061.35	5.57%
95	$3,834.23										$4,061.35
96	$3,834.23										$4,061.35
97	$3,834.23										$4,061.35
98	$3,834.23										$4,061.35
Splash

Cell Formulas
Range	Formula
AB1	AB1	=V1
AB2:AB98	AB2	=MAX(R2,AB1)
AC2:AC98	AC2	=IF(R3 < N(R2), 1-R2/AB2, "")
V4	V4	=V1+V5-V6
V5	V5	=SUMIF($N$2:$N$252,">0")
V6	V6	=SUMIF($N$2:$N$252,"<0")*-1
V7	V7	=AVERAGE(N2:N251)
V8	V8	=V5/V6
W9	W9	=COUNTIF(O2:O254, ">""")
W10	W10	=COUNTIF(P2:P254,">0")
W11	W11	=COUNTIF(P2:P254,"<0")
W12	W12	=MAX(P2:P254)
W13	W13	=MIN(P2:P254)
W14	W14	=AVERAGEIF(P2:P254, ">0")
W15	W15	=AVERAGEIF(P2:P254, "<0")
W16	W16	=COUNTIF(C2:C254, "buy")
W17	W17	=COUNTIF(C2:C254, "sell")
W18	W18	=COUNTIFS(C2:C254, "buy", N2:N254, ">0")
W19	W19	=COUNTIFS(C2:C254, "sell", N2:N254, ">0")
V20	V20	=MAX(FREQUENCY(IF(P2:P254>0,ROW(P2:P254)),IF(P2:P254<=0,ROW(P2:P254))))
V21	V21	=MAX(FREQUENCY(IF(P2:P254<0,ROW(P2:P254)),IF(P2:P254>=0,ROW(P2:P254))))
V22	V22	=MAX(Q2:Q254)
V23	V23	=MIN(Q2:Q254)
V24	V24	=SUM(K2:K254)
V26	V26	=W10/W9
V28	V28	=IF(R2:R200<4000,MAX(AC2:AC200))
V29	V29	=IF(R2:R200>4000,MAX(AC2:AC200))
T42	T42	=MAX($R$2:$R$252)*0.97
R2	R2	=V1+N2
R3:R98	R3	=R2+N3
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
S3:S200	Cell	contains a blank value	text	NO
A96:R200,O2:R95	Cell	contains a blank value	text	NO
A96:R200,O2:R95	Cell	contains a blank value	text	NO

 

[mrshl9898]

Must be missing something as R94 is less than 4000. Is 5.57% the result?

Would expect 1.11% from

=MAXIFS(AC:AC,R:R,">"&4000)

also, changing R18 to above 4000 doesn't affect your first result.

 

[andrewmurray86]

Thanks for the help. It is because the max peak has increased now, no longer working from 4000 but 4061.35 (R66)

I adjusted the Formula to =IF(AB2:AB200>4000,MAX(AC2:AC200)) cell v29 but still get false

 

[andrewmurray86]

Got it! I adjusted your formula to have AB:AB in stead of R:R and it worked... kind of... It's giving a slightly different value but it's working!