Streaks, maximum streaks for la

[DRSteele]

Can someone please help me?

I have over 43000 rows of data in an ExcelTable. I need an efficient forumla that determines streaks of zeros & ones and uses Excel Table nomenclature.

In this sample, there are two pieces of data (i.e., wtn high and wtn low) and four corresponding columns (in yellow) that require formulas.

For the streak high and streak low columns, I need this: If the data point is a 0, I want to show a -1 for the streak, and if the subsequent data point is 0, show -2, etc. If the data point is 1,

I want to show a 1 for the streak, and if the subsequent data point is 1, show 2, etc.

Then for the sh end and sl end columns, I want to show the maximum value of the current streak.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	K	L	N	O	P	Q
7	date	wtn high	wtn low	streak high	streak low	sh end	sl end
8	1/1/1900	0	0	-1	-1	0	0
9	1/2/1900	0	0	-2	-2	0	0
10	1/3/1900	0	0	-3	-3	0	0
11	1/4/1900	0	0	-4	-4	0	-4
12	1/5/1900	0	1	-5	1	-5	0
13	1/6/1900	1	1	1	2	1	0
14	1/7/1900	0	1	-1	3	-1	0
15	1/8/1900	1	1	1	4	0	4
16	1/9/1900	1	0	2	-1	0	-1
17	1/10/1900	1	1	3	1	0	0
18	1/11/1900	1	1	4	2	0	0
19	1/12/1900	1	1	5	3	0	0
20	1/13/1900	1	1	6	4	0	0
21	1/14/1900	1	1	7	5	0	0
22	1/15/1900	1	1	8	6	0	0
23	1/16/1900	1	1	9	7	0	0
24	1/17/1900	1	1	10	8	0	0
25	1/18/1900	1	1	11	9	0	0
26	1/19/1900	1	1	12	10	0	0
27	1/20/1900	1	1	13	11	0	0
28	1/21/1900	1	1	14	12	0	0
29	1/22/1900	1	1	15	13	0	0
30	1/23/1900	1	1	16	14	0	0
31	1/24/1900	1	1	17	15	0	15
32	1/25/1900	1	0	18	-1	18	-1
33	1/26/1900	0	1	-1	1	0	1
34	1/27/1900	0	0	-2	-1	-2	0
35	1/28/1900	1	0	1	-2	0	-2
Sheet1

_{^{<strike>
</strike>}}[/FONT]​

 

[Eric W]

How about:

	A	B	C	D	E	F	G
date	wtn high	wtn low	streak high	streak low	sh end	sl end

<tbody>
[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/1/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/2/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/3/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/4/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-4[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/5/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1/6/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1/7/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1/8/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1/9/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1/10/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1/11/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1/12/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1/13/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1/14/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1/15/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1/16/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1/17/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]1/18/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]1/19/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1/20/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]1/21/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]1/22/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]1/23/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1/24/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]1/25/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1/26/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1/27/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]1/28/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D8[/TH]
[TD="align: left"]=IF([@[wtn high]]=OFFSET([@[wtn high]],-1,0),(ABS(N(OFFSET([@[streak high]],-1,0)))+1),1)*IF([@[wtn high]]=0,-1,1)[/TD]
[/TR]
[TR]
[TH]E8[/TH]
[TD="align: left"]=IF([@[wtn low]]=OFFSET([@[wtn low]],-1,0),(ABS(N(OFFSET([@[streak low]],-1,0)))+1),1)*IF([@[wtn low]]=0,-1,1)[/TD]
[/TR]
[TR]
[TH]F8[/TH]
[TD="align: left"]=IF([@[wtn high]]=OFFSET([@[wtn high]],1,0),0,[@[streak high]])[/TD]
[/TR]
[TR]
[TH]G8[/TH]
[TD="align: left"]=IF([@[wtn low]]=OFFSET([@[wtn low]],1,0),0,[@[streak low]])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note that the last row differs in the last 2 columns, since the formulas are based on looking at the next row, which doesn't exist. And sorry, this uses the volatile OFFSET, but I don't know of another way to use table nomenclature to get previous/next rows.


Edit: you can use INDEX instead. Here's the F8 formula with INDEX:

=IF([@[wtn high]]=INDEX([wtn high],ROW([@[wtn high]])-ROW(INDEX([wtn high],1))+2),0,[@[streak high]])

 

[DRSteele]

Eric, forgive my late reply.

I can't thank you enough! The formula and daily query I had took over 21 minutes to run in the workbook with 43000+ rows, and Excel used the CPU constantly for the entire time, showing 'not responding' mode and occasionally crashing. Your formula took just 31 seconds.

Here is my old formula for 'streak high'. I think the expanding range was the culprit for bogging down everything. Function OFFSET seems immensely faster for some unexpected reason.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF([@[wtn high]],1,-1)*IF(COUNTIF($K$8:K8,$K$8)=COUNTA($K$8:K8),COUNTA($K$8:K8),MAX(ROW($K$8:K8))-MAX(($K$8:K8<>INDEX($K$8:K8,ROWS($K$8:K8)))*($K$8:K8<>"")*ROW($K$8:K8)))[/FONT]

I devised this formulas for 'sh end', which seems to work without it having to be altered for the final rows.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=MIN(ABS(IF(OFFSET([@[streak high]],1,)<>[@[streak high]]+1,[@[streak high]],0)),IF(OFFSET([@[streak high]],1,)<>[@[streak high]]-1,[@[streak high]],0))


[/FONT]

 

[Eric W]

You may be right about the expanding range, none of my formulas used more than 4 values total. Nice job updating the 'sh end' formulas to work for the final rows, I was going to tweak them after you tried them out, but I won't need to now!

I once read some comments from a programmer who worked at Microsoft about OFFSET. In short, he said that yes OFFSET is volatile, but it's very fast, so don't be afraid to use it. This may be a good example of that.

In any event, glad it works for you!

 

[DRSteele]

Terrific! I like OFFSET because it seems intuitive.

What I am doing here is trying to identify maximum streaks for above/below average outcomes of various kinds. The sample data I choose to experiment on is what we have here, Calgary's weather. I made a query that gets our historical weather back to 1/1/1900, calculates the mean highs and lows for each of the 366 days, compares each record to those means and then puts the table into an ExeclTable. I realise that the means include ALL the data (both ante and post each date) but I don't care.

Then the formulas we have here determine the streaks and others extract dates of neat things. For example, ending March 10, 2019 Calgary had lows that were below average for 38 consecutive days - what a rotten February we just had! Someone bleated to me that global warming is actually global cooling cuz that's some kind of record. But our record for days below average for the lows is 49 days, set in 1950.

I really don't care about the weather, but 43000 rows is enough to devise formulas like these. I am really using this to identify streaks for financial derivatives action in trade-by-trade data.