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), 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 |