How to: sum if only the most recent consecutive numbers in cells of same column appear.

Josean

New Member
Joined
Apr 6, 2022
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. MacOS
Hi all!

I'm preparing a trading journal where I want to see my Maximum Drawdown % (Max DD%) based on the MOST RECENT CONSECUTIVE TRADES that I have lost (considering I risked 1% of the account on each trade). Therefore, If I took 5 trades with the following results: LWLLL, I would like to see on the Maximum Drawdown % Cell the -3% (from the most recent 3 lost trades) instead of -4% (considering total losses). The reason of doing this is because, if my setup provides 1:4 risk to reward ratio, the first loss (-1%) should be recovered with the second trade that was a Win so I should not be in a drawdown for the next trade after the loss.

Most important columns of the chart are highlighted on the chart in red: K2 and O column (DD%).
Other detail to know:
For the O1 column, I used the formula: =IF(M8<0, $M8/H$2,"") THEREFORE NO 0 OR POSITIVE VALUES WILL APPEAR on the cell.

Not sure if I'm asking for too much here but thanks in advance!

ABCDEFGHIJKLMNOP
1Total TradesTotal WinsTotal LossesBreak EvenWinning %Losing %BE %Starting BalanceTotal $ GainTotal % GainMax DD %Avg RRR
2514020%80%0%$ 100,000.00$ -
0.0%​
-4.0%
4​
3
4# of TradesPAIR/ASSETNY TimeDateSessionStop Loss (Pips/Units)Risk % Per PositionRisk $ Per PositionRRR (1:X)Account PipsAccount Cash% Gain/LossP&LP&L%DD %Win/Loss
51USDJPY12:05 AM04/04/22London101.0%$ 1,000.00-1-10$ 99,000.00-1.0%-1,000-1.0%-1.0%L
6204/05/22101.0%$ 1,000.00440$ 103,000.003.0%4,0004.0%W
7304/06/22101.0%$ 1,000.00-1-10$ 102,000.002.0%-1,000-1.0%-1.0%L
8404/07/22101.0%$ 1,000.00-1-10$ 101,000.001.0%-1,000-1.0%-1.0%L
9​
504/08/22101.0%$ 1,000.00-1-10$ 100,000.000.0%-1,000-1.0%-1.0%L

-Josean
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Give this a shot (It's finding the last blank value in a range, and the summing all the values below that blank value):
Excel Formula:
=SUMPRODUCT($O$5:$O$9*(ROW($O$5:$O$9)>SUMPRODUCT(MAX(ROW($O$5:$O$9)*(""=$O$5:$O$9)))))
 
Upvote 0
Give this a shot (It's finding the last blank value in a range, and the summing all the values below that blank value):
Excel Formula:
=SUMPRODUCT($O$5:$O$9*(ROW($O$5:$O$9)>SUMPRODUCT(MAX(ROW($O$5:$O$9)*(""=$O$5:$O$9)))))
MasterGoal Journal .ods
ABCDEFGHIJKLMNO
1Total TradesTotal WinsTotal LossesBreak EvenWinning %Losing %BE %Starting BalanceTotal $ GainTotal % GainMax DD %Avg RRR
2514020%80%0%$ 100,000.00$ -0.0%#VALUE!4
3
4# of TradesPAIR/ASSETNY TimeDateSessionStop Loss (Pips/Units)Risk % Per PositionRisk $ Per PositionRRR (1:X)Account Pips/UnitsAccount Cash% Gain/LossP&LP&L%DD %
51USDJPY12:05 AM04/04/22London101.0%$ 1,000.00-1-10$ 99,000.00-1.0%-1,000-1.0%-1.0%
6204/05/22101.0%$ 1,000.00440$ 103,000.003.0%4,0004.0% 
7304/06/22101.0%$ 1,000.00-1-10$ 102,000.002.0%-1,000-1.0%-1.0%
8404/07/22101.0%$ 1,000.00-1-10$ 101,000.001.0%-1,000-1.0%-1.0%
9504/08/22101.0%$ 1,000.00-1-10$ 100,000.000.0%-1,000-1.0%-1.0%
MasterGoal Yearly Journal
Cell Formulas
RangeFormula
A2A2=B2+C2+D2
B2B2=COUNTIF(Q5:Q1004, "W")
C2C2=COUNTIF(Q5:Q1004,"L")
D2D2=COUNTIF(Q5:Q1004, "BE")
E2E2=B2/A2
F2F2=C2/A2
G2G2=D2/A2
I2I2=SUM(M5:M1004)
J2J2=I2/H2
K2K2=SUMPRODUCT($O$5:$O$1004*(ROW($O$5:$O$1004)>SUMPRODUCT(MAX(ROW($O$5:$O$1004)*(""=$O$5:$O$1004)))))
L2L2=AVERAGEIF(I5:I1004,">0")
J5:J9J5=F5*I5
K5K5=H2+(I5*H5)
L5L5=($H5*I$5)/$H2
M5:M9M5=H5*I5
N5N5=$M5/H$2
O5:O9O5=IF(M5<0, $M5/H$2,"")
K6:K9K6=IF(H6>0,K5+($I6*H$6),"")
L6:L9L6=IF(H6>0,($K6-H$2)*0.00001,"")
N6:N9N6=IF(H6>0,$M6/H$2,"")
H5H5=G5*H2
H6H6=G6*H2
H7H7=G7*H2
H8H8=G8*H2
H9H9=G9*H2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G5Cell Value=0.005textNO
E5:E1004Cell Value="Weekend"textYES
E5:E1004Cell Value="NY After Lunch"textYES
E5:E1004Cell Value="NY"textYES
E5:E1004Cell Value="London"textYES
E5:E1004Cell Value="Asia"textYES
Cells with Data Validation
CellAllowCriteria
E5:E9ListN/A,Asia,London,NY,NY After Lunch,Weekend
C5:C9Timebetween 12:00:00 AM and 11:59:00 PM
G5:G9List0.25%,0.30%,0.40%,0.50%,0.60%,0.75%,1%,1.25%,1.5%,1.75%,2%,2.5%,3%,3.5%,4%
 
Upvote 0
Give this a shot (It's finding the last blank value in a range, and the summing all the values below that blank value):
Excel Formula:
=SUMPRODUCT($O$5:$O$9*(ROW($O$5:$O$9)>SUMPRODUCT(MAX(ROW($O$5:$O$9)*(""=$O$5:$O$9)))))
Hi Max, I appreciate your help! However, the code did not work this time, I included the minisheet to see if this is helpful, thanks!
 
Upvote 0
Two things to try:
1: Change the formula to this:
Excel Formula:
=SUMPRODUCT($O$5:$O$1004*(ROW($O$5:$O$1004)>SUMPRODUCT(MAX(ROW($O$5:$O$1004)*(0=$O$5:$O$1004)*(K5:K1004<>"")))))

2: Change the formula in column O to this (The "" is throwing the formula off for some reason):
Excel Formula:
=IF(M5<0, $M5/H$2,0)

This is working correctly in my test workbook.
 
Upvote 0
Two things to try:
1: Change the formula to this:
Excel Formula:
=SUMPRODUCT($O$5:$O$1004*(ROW($O$5:$O$1004)>SUMPRODUCT(MAX(ROW($O$5:$O$1004)*(0=$O$5:$O$1004)*(K5:K1004<>"")))))

2: Change the formula in column O to this (The "" is throwing the formula off for some reason):
Excel Formula:
=IF(M5<0, $M5/H$2,0)

This is working correctly in my test workbook.
Resolved! This data is important for me to follow so the "" function is not a big deal, thank you so much for your your great help!
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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