Moving Streaks

[DRSteele]

Can someone please help me?

I wish to calculate the number of occurrences for each trader where the streak of the declared number of trades (I used the variable 5 here) resulted in a winning percentage greater than the hurdle rate (I used 60% here). So for Dan, there was only one time (transactions 1 through 5) where any string of 5 trades resulted in at least 60% of the points available (which is 5x2=10 pts). Cam succeeded in all 16 of the possible 5-transaction strings.

I manually reckoned the answers in ColumnV by selecting every possible string of 5 transactions for each trader and then determining from the status bar at the bottom of Excel how many strings summed to 6 or greater. But I would really like a single formula in ColumnV to report the number of successes for each trader; in this example that number is between 0 and 16.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	trader // transaction	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	surpass hurdle?
2	abe	1	1	1	2	0	2	1	0	0	1	1	2	1	0	2	2	2	1	2	0	yes, 8 times
3	bob	0	0	1	1	1	0	0	1	2	0	0	0	1	1	1	1	0	2	0	2	no
4	cam	2	2	1	2	2	2	2	1	2	0	2	2	2	2	1	1	2	2	2	1	yes, all 16 times
5	dan	1	1	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	yes, 1 time
6	edd	2	0	2	0	1	0	2	1	1	2	0	1	0	2	1	0	2	2	1	2	yes, 4 times
7
8	hurdle	60%
9	for trades	5
10	max points per trade	2

<tbody>
</tbody>

Sheet14​

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	trader // transaction	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	surpass hurdle?
2	abe	1	1	1	2	0	2	1	0	0	1	1	2	1	0	2	2	2	1	2	0	yes, 6 times	8
3	bob	0	0	1	1	1	0	0	1	2	0	0	0	1	1	1	1	0	2	0	2	no	0
4	cam	2	2	1	2	2	2	2	1	2	0	2	2	2	2	1	1	2	2	2	1	yes, all 16 times	16
5	dan	1	1	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	yes, 1 time	1
6	edd	2	0	2	0	1	0	2	1	1	2	0	1	0	2	1	0	2	2	1	2	yes, 4 times	5
7
8	hurdle	60%
9	for trades	5
10	max points per trade	2

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula W2 {=SUM(IF(SUBTOTAL(9,OFFSET(B2,0,ROW(INDIRECT("1:"&21-$B$9))-1,1,$B$9))>=$B$9*$B$10*$B$8,1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

My totals are slightly different from your example, if mine are wrong, let me know. Also note that the 21 in the formula is 1 more than the number of columns, so if you add more columns, that will need to be changed.

 

[DRSteele]

Eric, thanks very much for the quick reply.

Yes, I made a mistake in my manual reckoning: the proper results are 8, 0, 16, 1, 5. For too many hours I banged my head in the wall with OFFSET and INDIRECT and finally decided to seek help.

I see that SUBTOTAL is the key but I can't understand it. I evaluate ref1 inside SUBTOTAL and it reports the array of values from columns B through U. Then I try to evaluate the entire SUBTOTAL portion and it reports error. But when I evaluate the logical_test argument of IF it reports meaningful TRUES and FALSES. I evaluate the SUBTOTAL portion without evaluating its internal arguments first and I get an accurate array of performance percentages. So I guess SUBTOTAL is one of those functions that can't be dissected, right? There must be a reason you chose SUBTOTAL instead of SUM or AGGREGATE.

In any event, you are extremely clever...what a simple solution to something so seemingly complex. THANKS Eric!

 

[Eric W]

You are correct, SUBTOTAL is the key. It has some nice properties. In order to check multiple ranges in a single formula, it needs to be an array formula. We can use the array inside of an OFFSET to calculate the various ranges. But that's where it usually breaks downs. Arrays usually don't play nice with LOOKUP type functions, OFFSET, INDIRECT, INDEX, VLOOKUP, etc. But there is an exception, if the array OFFSET is inside SUBTOTAL, it will work. But as you noticed, it's still a bit odd. If you run it through the Evaluate Formula tool, it generates a lot of #VALUE errors. But if you go one more step, the right totals show up. This makes it hard to debug sometimes. I had 2 parameters reversed in my first version of the OFFSET, and it was hard to figure out, since the step where I would have found the problem didn't show up. You really have to make sure that you code the OFFSET correctly.

Anyway, glad I could help!

 

[DRSteele]

Thanks for expanding on this concept for us, Eric. This algorithm is working just right on my massive data.