Hi all,
I'm struggling with adding an additional criteria into a existing formula...
I have already figured out how I can count consecutive WIN/LOSS. (Formula in cell F17)
However, I must add one more criteria.
The formula in cell H17 should count consecutive WIN/LOSS of "S" from column L.
Please note: I'm using a german version so the formula contains ';' instead of ','
Thanks for any help!
I think that you are over complicating this.
This Formula will count occurrences of S followed by S (SS) =5
=LEN(TEXTJOIN(,TRUE,Tab_RawData[L / S]))-LEN(SUBSTITUTE(TEXTJOIN(,TRUE,Tab_RawData[L / S]),"SS","S"))
What would happen if there are three S's in sequence. Does that count as One repetition or two?
I have Checked and the formula does not recognise (SSS) or (SSSS) but I don't know how you want then counted.
If you want "SSS" counted as 2 then use SUBSTITUTE(TEXTJOIN(,TRUE,Tab_RawData[L / S]),"SSS","SS:SS") inside the Formulas above.
If you want "SSSS" counted as 3 then use SUBSTITUTE(TEXTJOIN(,TRUE,Tab_RawData[L / S]),"SSSS","SS:SS:SS") inside the Formulas above.
Last edited by mehmetcik; 02-29-2020 at 03:03 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
I probably didn't explain it clearly.
Just for better explaination:
S from column L stands for Short
L stands for Long
The formula:
...counts all the consecutive "WIN" from column P. So far so good...
Please Login or Register to view this content.
Now, I additionally need a counter of the consecutive WINS just for the Shorts. (if S in column L).
So it should only count the consecutive WINS when in column L is a 'S'.
Formula Entered using Ctrl Shift Enter.
=(LEN(TEXTJOIN("",TRUE,Tabelle2[L / S]&Tabelle2[Win / LOSS]))-LEN(SUBSTITUTE(TEXTJOIN("",TRUE,Tabelle2[L / S]&Tabelle2[Win / LOSS]),"LWINLWIN","LWIN")))/4
Mehmet thank you for your fast reply.
Unfortunately it doesn't show the right result.
It shows 5 instead of 4.
When I filter my table to show only 'S' then it has Max. 4 consecutive WINS. (Trade # 18, 20, 22 and 23)
Winding this back a bit. My Formula was wrong. I Got distracted. It was looking for the wrong Match.
This formula Ctrl Shift Enter Returns 10.
So there are 10 Short Wins. Not 4 as you say. 53 and 55 are also matches for example.
=SUM(IF(Tabelle2[L / S]&Tabelle2[Win / LOSS]="SWIN",1,0))
However is only one sequential Short Win. 22 and 23.
This Formula Entered using Ctrl Shift Enter Returns 1.
=(LEN(TEXTJOIN("",TRUE,Tabelle2[L / S]&Tabelle2[Win / LOSS]))-LEN(SUBSTITUTE(TEXTJOIN("",TRUE,Tabelle2[L / S]&Tabelle2[Win / LOSS]),"SWINSWIN","SWIN")))/4
Last edited by mehmetcik; 02-29-2020 at 05:31 PM.
how about below array entered
Formula:
Please Login or Register to view this content.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
It should not calculate the summary of all WINS for Short.
It should calculate the maximum consecutive WINS in a row for Short.
As you can see in the picture before, Trade# 18, 20, 22 and 23 was a WIN. (4 WIN trades in a row) Trade # 25 was LOSS, so the counter goes back to 0.
Another example:
1) LOSS
2) WIN
3) WIN
4) WIN
5) WIN
6) WIN
7) WIN
8) WIN
9) LOSS
For that example it would be: 7 consecutive WINS.
This 7 consecutive WINS would be the maximum consecutive WINS until it exceed more then 7 WINS in a row.
18, 20, 22 are not Sequential.
That's true in terms of Trade# but if you look just for "Short" they are sequential.
I was trying this formula
But it display "False"Please Login or Register to view this content.
Hello rtprcnb,
Have you tried formula from POST#7
Hi Hemesh,
Yes but it shows an error.
check the attachment its working fine here !
I have added more data in the table below as a test. Now the formula should show the number 6, but it still shows 4.
the formula provided is looking for sequential on the basis of month that in which particular month maximum "S" with "Win" are present
Hi! rtprcnb
=MAX(FREQUENCY(IF((Tabelle2[L / S]="S")*(Tabelle2[Win / LOSS]="WIN"),ROW(Tabelle2[Win / LOSS])),IF((Tabelle2[L / S]="L")+(Tabelle2[Win / LOSS]="LOSE"),ROW(Tabelle2[Win / LOSS]))))
Hi Chief_abound!
Your formula nearly works...
I have inserted the file.
The green fields are correct, the red fields are not right. Probably some small twist in the formula is needed.
In cell H17 should be 4 instead of 2
In cell G18 should be 6 instead of 5
(Cell G17 and H18 are correct)
I have already tried different variations but nothing works.
Net Profit Trades Total Number Winning Trades Number Losing Trades Avg Trade (win/loss) Avg Winning Trade Avg Losing Trade Percent Profitable Avg Monthly Return (PIVOT?) Largest Winning Trade Largest Losing Trade WIN LOSS
G17=MAX(FREQUENCY(IF($P$6:$P$62=$E17,IF($L$6:$L$62=G$5,ROW($P$6:$P$62))),IF($P$6:$P$62<>$E17,IF($L$6:$L$62=G$5,ROW($P$6:$P$62)))))
Control+shift+enetr
Copy across and down
Ciao CARACALLA,
It's working
Grazie mille!! Thank you very much!
Thanks for the feedback !
Ciao
You are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks