Identify rows where value exceeds hurdle consecutively

[DRSteele]

Can someone please help me?

I need a formula for the red column. I wish to identify the rows where the Value equals or exceeds 20 and is part of a streak of at least 4 rows where the Value equals or exceeds 20. Here I used 1s and 0s in a helper column to identify the desired result. Eventually, I would like the formula to be usable in Conditional Formatting and eliminate the need for the helper column.

00 HTML Conversions.xlsm
	A	B
1	streak	4
2	hurdle	20
3
4	Value	Part of Streak?
5	21.0	1
6	21.0	1
7	21.0	1
8	20.0	1
9	18.3	0
10	16.7	0
11	22.8	0
12	25.0	0
13	0.0	0
14	22.2	1
15	25.0	1
16	28.9	1
17	20.0	1
18	17.8	0
19	16.1	0
20	20.0	1
21	23.3	1
22	30.0	1
23	20.0	1
24	16.1	0
25	15.6	0
26	8.9	0
27	13.9	0
28	16.7	0
29	20.0	1
30	22.8	1
31	22.2	1
32	20.6	1
33	22.8	1
34	30.0	1
Sheet18

 

[DRSteele]

Thanks everyone for all the replies, including Rick, Spiller and Sheetspread. (I don't know why I hadn't been notified by email.)

Peter, I have 43000 rows of data. Using the formula you provided in any column (to make a T/F for each row) slows the speadsheet recalc to a crawl. Using it as a CondFrmt rule does even worse. Frequently, Excel just crashes outright. I suspect it's the IFERROR part combined with the two expanding and contracting ranges.

I sure wish I could figure out an efficient formula to reckon streaks of various lengths across large data sets. Even a Power Query solution would be acceptable. I can't use VBA though, as Rick and Sheetspread suggested in their posts.

 

[sheetspread]

I can't use VBA

If you say so, but this code worked on a table of 43,000 rows in just a few seconds:

Sub hilite()
Dim i As Long, j As Long, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To lr
j = 0
Do Until Cells(i + j, 1) < Range("b2").Value
j = j + 1
Loop
If j >= Range("b1").Value Then
Cells(i, 1).Resize(j).Interior.ColorIndex = 4
i = i + j
Else
End If
Next
End Sub

(the previous version required a variable type change for the larger table and removing the +1 from line 11)

 

[DRSteele]

If you say so, but this code worked on a table of 43,000 rows in just a few seconds:]

You misunderstand: I may not use VBA; prohibited; verboten; not allowed.

 

[sheetspread]

I knew it was a client or coworker's objection rather than any technical limit. I don't have Power Query right now, but try posting to the BI forum with this thread's link if you haven't already.

 

[DRSteele]

Thanks, sheetspread. I will do so.

 

[Peter_SSs]

Peter, I have 43000 rows of data. Using the formula you provided in any column (to make a T/F for each row) slows the speadsheet recalc to a crawl.

See if this is a bit lighter on the resources. For my sample data it re-calculated a 43,000 row range in a few hundredths of a second.

Excel Workbook
	A	B
1	streak	4
2	hurdle	20
3
4	Value	Streak?
5	16	0
6	13	0
7	41	1
8	27	1
9	41	1
10	23	1
11	16	0
12	43	0
13	35	0
14	37	0
15	4	0
16	5	0
17	29	1
18	24	1
19	46	1
20	34	1
21	43	1
22	44	1
23	15	0
24	46	0
25	4	0
26	28	1
27	26	1
28	44	1
29	36	1
30	19	0
Streaks (2)

 

[DRSteele]

Peter, that is a vast improvement. Super...thanks tons.

Just so you lot know what I'm on about: I have three practical reasons to pick out streaks. First, on small datasets like the NHL, I want to identify winning & losing streaks for each team in the league. Second, I want to identify heat-waves and cold-snaps in weather data (hence the 43000 rows for daily temps from 1900 onwards). Third and most important, I want to pick out profit streaks for high-frequency stock-market & derivative trading algorithms, which entails millions of rows.

 

[Peter_SSs]

Peter, that is a vast improvement. Super...thanks tons.

You're welcome.