Excluding cells from calculations based on threshold

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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
RSTUVWXYZAAABACAD
1EquityBeginning Equity4000$4,000.00
2$3,995.97$4,000.000.10%
3$3,991.93Updated when trade is closed$4,000.000.20%
4$3,988.28Realized Profit/Loss3834.23Profit/Loss of all closed trades$4,000.000.29%
5$3,984.64Gross profit669.63Total profit of all won trades$4,000.000.38%
6$3,980.38Gross loss835.4Total loss of all lost trades$4,000.000.49%
7$3,976.13Expected payoff-$1.76Average trade outcome$4,000.000.60%
8$3,936.21Profit factor0.801568111Gross profit / gross loss$4,000.001.59%
9$3,896.30Number of closed tradesPer Year57$4,000.002.59%
10$3,896.14Profit trades32$4,000.002.60%
11$3,895.98Loss trades25$4,000.002.60%
12$3,867.16Largest profit trade$94.11$4,000.003.32%
13$3,838.39Largest loss trade-$42.44$4,000.004.04%
14$3,798.00Average profit trade$20.90$4,000.005.05%
15$3,757.61Average loss trade-$24.68$4,000.006.06%
16$3,716.97Long trades32$4,000.007.08%
17$3,676.33Short trades25$4,000.008.09%
18$3,675.41Long trades won24$4,000.008.11%
19$3,675.09Short trades won8$4,000.00 
20$3,681.66Max. consecutive wins6Longest winning streak (number of trades)$4,000.00 
21$3,688.27Max. consecutive losses8Longest losing streak (number of trades)$4,000.00 
22$3,764.01Max. consecutive profit198.29Largest winning streak (total profit)$4,000.00 
23$3,788.03Max. consecutive loss-219.65Largest losing streak (total loss)$4,000.00 
24$3,812.48Paid commissions0$4,000.00 
25$3,812.78$4,000.004.68%
26$3,812.30Win Rate56.14%$4,000.00 
27$3,837.65$4,000.00 
28$3,931.76Max Drawdown 8.11%$4,000.00 
29$3,949.48Relative DrawdownFALSE$4,000.00 
30$4,010.59$4,010.590.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.770.00%
36$4,053.31$4,053.770.01%
37$4,053.07$4,053.770.02%
38$4,052.70$4,053.770.03%
39$4,052.45$4,053.770.03%
40$4,017.59$4,053.770.89%
41$3,982.72$4,053.771.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.770.10%
47$4,048.68$4,053.770.13%
48$4,048.31$4,053.770.13%
49$3,948.31$4,053.772.60%
50$3,928.31$4,053.773.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.771.89%
56$3,960.06$4,053.772.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.770.33%
64$4,039.76$4,053.77 
65$4,060.98$4,060.98 
66$4,061.35$4,061.350.00%
67$4,059.70$4,061.350.04%
68$4,059.12$4,061.350.05%
69$4,058.87$4,061.350.06%
70$4,058.61$4,061.350.07%
71$4,016.17$4,061.351.11%
72$3,973.76$4,061.352.16%
73$3,973.47$4,061.352.16%
74$3,940.03$4,061.352.99%
75$3,906.59$4,061.35 
76$3,932.69$4,061.353.17%
77$3,932.20$4,061.353.18%
78$3,864.20$4,061.354.85%
79$3,861.67$4,061.35 
80$3,887.37$4,061.354.28%
81$3,885.99$4,061.354.32%
82$3,884.94$4,061.354.34%
83$3,883.92$4,061.354.37%
84$3,882.93$4,061.354.39%
85$3,881.94$4,061.35 
86$3,882.93$4,061.354.39%
87$3,879.87$4,061.354.47%
88$3,879.08$4,061.354.49%
89$3,878.04$4,061.354.51%
90$3,877.05$4,061.354.54%
91$3,876.22$4,061.354.56%
92$3,857.20$4,061.355.03%
93$3,838.17$4,061.355.50%
94$3,835.12$4,061.355.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
RangeFormula
AB1AB1=V1
AB2:AB98AB2=MAX(R2,AB1)
AC2:AC98AC2=IF(R3 < N(R2), 1-R2/AB2, "")
V4V4=V1+V5-V6
V5V5=SUMIF($N$2:$N$252,">0")
V6V6=SUMIF($N$2:$N$252,"<0")*-1
V7V7=AVERAGE(N2:N251)
V8V8=V5/V6
W9W9=COUNTIF(O2:O254, ">""")
W10W10=COUNTIF(P2:P254,">0")
W11W11=COUNTIF(P2:P254,"<0")
W12W12=MAX(P2:P254)
W13W13=MIN(P2:P254)
W14W14=AVERAGEIF(P2:P254, ">0")
W15W15=AVERAGEIF(P2:P254, "<0")
W16W16=COUNTIF(C2:C254, "buy")
W17W17=COUNTIF(C2:C254, "sell")
W18W18=COUNTIFS(C2:C254, "buy", N2:N254, ">0")
W19W19=COUNTIFS(C2:C254, "sell", N2:N254, ">0")
V20V20=MAX(FREQUENCY(IF(P2:P254>0,ROW(P2:P254)),IF(P2:P254<=0,ROW(P2:P254))))
V21V21=MAX(FREQUENCY(IF(P2:P254<0,ROW(P2:P254)),IF(P2:P254>=0,ROW(P2:P254))))
V22V22=MAX(Q2:Q254)
V23V23=MIN(Q2:Q254)
V24V24=SUM(K2:K254)
V26V26=W10/W9
V28V28=IF(R2:R200<4000,MAX(AC2:AC200))
V29V29=IF(R2:R200>4000,MAX(AC2:AC200))
T42T42=MAX($R$2:$R$252)*0.97
R2R2=V1+N2
R3:R98R3=R2+N3
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
S3:S200Cellcontains a blank value textNO
A96:R200,O2:R95Cellcontains a blank value textNO
A96:R200,O2:R95Cellcontains a blank value textNO
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top