Finding Rates, counting, prioritization and panel mounting

[komhs]

Good evening everybody,

I need help creating a table that will measure consecutive lost trades and consecutive won trades, as well as their unique values. In short: what is the largest series of lost trades I have had and how many times this has occurred?

In the attached file I have a list of the won and lost trades, but I have found a non-productive way to calculate them by generating unnecessary columns. Can you help me to avoid it?
Also, I would like this table to have an even distribution between the values of the losing streak and the winning streak (and start from 1), so if I use another dataset with trades, I would like them to fit accordingly.

I believe that you will understand perfectly when you will take a look at the excel file.

Thanks in advance for your help

[FlameRetired]

Try array entering this formula in H14 for the MAX win streaks.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then retain the formulas you have in H15:H22. Then to get the counts of frequencies array enter this in I14 and fill down.

Please Login or Register  to view this content.

Follow the same logic substituting "lost" for "won" in the formulas to get the losing data.

[komhs]

Thank you very much for your answer. You saved me for generating columns, but unfortunately that doesn't solve the problem with the table... The reason is that if I start counting backwards (from the max winning or losing) I will not be able to keep an even distribution between the values of winning and losing streak.

So I will face problems like this...

CONSEC. WINNING TRADES	TOTAL TIMES	CONSEC. LOSING TRADES	TOTAL TIMES
9	1	18	1
8	0	17	1
7	0	16	1
6	3	15	1
5	3	14	1
4	6	13	1
3	7	12	1
2	23	11	1
1	58	10	1
0	0	9	2
0	0	8	1
0	0	7	2
0	0	6	2
0	0	5	8
0	0	4	12
0	0	3	18
0	0	2	17
0	0	1	39

But in order to keep a Balance on the table's values it must start from 1 and counting. Like this

CONSEC. WINNING TRADES	TOTAL TIMES	CONSEC. LOSING TRADES	TOTAL TIMES
1	58	1	39
2	23	2	17
3	7	3	18
4	6	4	12
5	3	5	8
6	3	6	2
7	0	7	2
8	0	8	1
9	1	9	2
0	0	10	1
0	0	11	1
0	0	12	1
0	0	13	1
0	0	14	1
0	0	15	1
0	0	16	1
0	0	17	1
0	0	18	1

[Pepe Le Mokko]

Please post a sheet instead of text. Thanks

[komhs]

Thank you very much for your answer. You saved me for generating columns, but unfortunately that doesn't solve the problem with the table... The reason is that if I start counting backwards (from the max winning or losing) I will not be able to keep an even distribution between the values of winning and losing streak.

So I will face problems like this...

CONSEC. WINNING TRADES	TOTAL TIMES	CONSEC. LOSING TRADES	TOTAL TIMES
9	1	18	1
8	0	17	1
7	0	16	1
6	3	15	1
5	3	14	1
4	6	13	1
3	7	12	1
2	23	11	1
1	58	10	1
0	0	9	2
0	0	8	1
0	0	7	2
0	0	6	2
0	0	5	8
0	0	4	12
0	0	3	18
0	0	2	17
0	0	1	39

But in order to keep a Balance on the table's values it must start from 1 and counting. Like this

CONSEC. WINNING TRADES	TOTAL TIMES	CONSEC. LOSING TRADES	TOTAL TIMES
1	58	1	39
2	23	2	17
3	7	3	18
4	6	4	12
5	3	5	8
6	3	6	2
7	0	7	2
8	0	8	1
9	1	9	2
0	0	10	1
0	0	11	1
0	0	12	1
0	0	13	1
0	0	14	1
0	0	15	1
0	0	16	1
0	0	17	1
0	0	18	1

Thanks for your reply
I inserted and a excel file

[komhs]

I attached a file

[FlameRetired]

Please see the attached. I put the WON/LOST across row 11. That way the formulas can use them by reference. This enables copying the won formula ranges and pasting them into the lost ranges.

Array entered in H13 and filled down.

Please Login or Register  to view this content.

Array entered in I13 and filled down.

Please Login or Register  to view this content.

Then copy H13:I26 and paste into cell J13.

[komhs]

Thank you so much!!!!

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.