andrewmurray86
New Member
 Joined
 Jun 18, 2020
 Messages
 29
 Office Version

 2016
 Platform

 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??
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??
Cell Formulas  

Range  Formula  
AB1  AB1  =V1 
AB2:AB98  AB2  =MAX(R2,AB1) 
AC2:AC98  AC2  =IF(R3 < N(R2), 1R2/AB2, "") 
V4  V4  =V1+V5V6 
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 