Streaks, maximum streaks for la

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,294
Office Version
  1. 365
Platform
  1. Windows
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]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>K</th><th>L</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="background-color: #9BC2E6;;">date</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #C00000;;">wtn high</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #002060;;">wtn low</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #C00000;;">streak high</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #002060;;">streak low</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #C00000;;">sh end</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #002060;;">sl end</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1/1/1900</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFF2CC;;">-1</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFE699;;">-1</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFF2CC;;">0</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1/2/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">-2</td><td style="text-align: right;background-color: #FFE699;;">-2</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1/3/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">-3</td><td style="text-align: right;background-color: #FFE699;;">-3</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1/4/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">-4</td><td style="text-align: right;background-color: #FFE699;;">-4</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">-4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1/5/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">-5</td><td style="text-align: right;background-color: #FFE699;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">-5</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1/6/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">1</td><td style="text-align: right;background-color: #FFE699;;">2</td><td style="text-align: right;background-color: #FFF2CC;;">1</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1/7/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">-1</td><td style="text-align: right;background-color: #FFE699;;">3</td><td style="text-align: right;background-color: #FFF2CC;;">-1</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1/8/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">1</td><td style="text-align: right;background-color: #FFE699;;">4</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1/9/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">2</td><td style="text-align: right;background-color: #FFE699;;">-1</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">-1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1/10/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">3</td><td style="text-align: right;background-color: #FFE699;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1/11/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">4</td><td style="text-align: right;background-color: #FFE699;;">2</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">1/12/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">5</td><td style="text-align: right;background-color: #FFE699;;">3</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">1/13/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">6</td><td style="text-align: right;background-color: #FFE699;;">4</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">1/14/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">7</td><td style="text-align: right;background-color: #FFE699;;">5</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">1/15/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">8</td><td style="text-align: right;background-color: #FFE699;;">6</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">1/16/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">9</td><td style="text-align: right;background-color: #FFE699;;">7</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">1/17/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">10</td><td style="text-align: right;background-color: #FFE699;;">8</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">1/18/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">11</td><td style="text-align: right;background-color: #FFE699;;">9</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;">1/19/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">12</td><td style="text-align: right;background-color: #FFE699;;">10</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;">1/20/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">13</td><td style="text-align: right;background-color: #FFE699;;">11</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;">1/21/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">14</td><td style="text-align: right;background-color: #FFE699;;">12</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;">1/22/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">15</td><td style="text-align: right;background-color: #FFE699;;">13</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;">1/23/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">16</td><td style="text-align: right;background-color: #FFE699;;">14</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;">1/24/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">17</td><td style="text-align: right;background-color: #FFE699;;">15</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;">1/25/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">18</td><td style="text-align: right;background-color: #FFE699;;">-1</td><td style="text-align: right;background-color: #FFF2CC;;">18</td><td style="text-align: right;background-color: #FFE699;;">-1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;">1/26/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">-1</td><td style="text-align: right;background-color: #FFE699;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style="text-align: right;;">1/27/1900</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">-2</td><td style="text-align: right;background-color: #FFE699;;">-1</td><td style="text-align: right;background-color: #FFF2CC;;">-2</td><td style="text-align: right;background-color: #FFE699;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style="text-align: right;;">1/28/1900</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">1</td><td style="text-align: right;background-color: #FFE699;;">-2</td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFE699;;">-2</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
<strike>
</strike>
[/FONT]
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,228
How about:

ABCDEFG
7datewtn highwtn lowstreak highstreak lowsh endsl end
81/1/190000-1-100
91/2/190000-2-200
101/3/190000-3-300
111/4/190000-4-40-4
121/5/190001-51-50
131/6/1900111210
141/7/190001-13-10
151/8/1900111404
161/9/1900102-10-1
171/10/1900113100
181/11/1900114200
191/12/1900115300
201/13/1900116400
211/14/1900117500
221/15/1900118600
231/16/1900119700
241/17/19001110800
251/18/19001111900
261/19/190011121000
271/20/190011131100
281/21/190011141200
291/22/190011151300
301/23/190011161400
311/24/1900111715015
321/25/19001018-118-1
331/26/190001-1101
341/27/190000-2-1-20
351/28/1900101-210

<tbody>
</tbody>
Sheet10

Worksheet Formulas
CellFormula
D8=IF([@[wtn high]]=OFFSET([@[wtn high]],-1,0),(ABS(N(OFFSET([@[streak high]],-1,0)))+1),1)*IF([@[wtn high]]=0,-1,1)
E8=IF([@[wtn low]]=OFFSET([@[wtn low]],-1,0),(ABS(N(OFFSET([@[streak low]],-1,0)))+1),1)*IF([@[wtn low]]=0,-1,1)
F8=IF([@[wtn high]]=OFFSET([@[wtn high]],1,0),0,[@[streak high]])
G8=IF([@[wtn low]]=OFFSET([@[wtn low]],1,0),0,[@[streak low]])

<tbody>
</tbody>

<tbody>
</tbody>



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]])
 
Last edited:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,294
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,228
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,294
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,271
Messages
5,527,708
Members
409,784
Latest member
lalz1205

This Week's Hot Topics

Top