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

 

[JoeMo]

Here's a CF rule that highlights the first cell of each group of (or whatever is in B1).

Excel Workbook
	A	B
1	streak	4
2	hurdle	20
3
4	Value
5	21
6	21
7	21
8	20
9	18.3
10	16.7
11	22.8
12	25
13	0
14	22.2
15	25
16	28.9
17	20
18	17.8
19	16.1
20	20
21	23.3
22	30
23	20
24	16.1
25	15.6
26	8.9
27	13.9
28	16.7
29	20
30	22.8
31	22.2
32	20.6
33	22.8
34	30
Sheet1

The rule:

=AND($A5>=$B$2,COUNTIF(OFFSET($A5,0,0,$B$1,1),">="&$B$2)=$B$1)

 

[DRSteele]

Joe, thank you very much for your reply.

I would really like to identify each value that is part of the streak, not just the first value. Is that possible?

 

[JoeMo]

Joe, thank you very much for your reply.

I would really like to identify each value that is part of the streak, not just the first value. Is that possible?

Probably, but the rule is not apparent to me just now.

 

[SpillerBD]

Four rules?

 

[Rick Rothstein]

Assuming the layout you showed in Message #1 , here is a macro that will produce the column of zeros and ones in Column B that you showed you wanted in that post...

Sub IdentifyStreaks()
  Dim X As Long, S As String, Streak() As String
  S = Join(Evaluate(Replace("TRANSPOSE(0+(A5:A#>=B2))", "#", Cells(Rows.Count, "A").End(xlUp).Row)), "")
  Streak = Split(S, "0")
  For X = 0 To UBound(Streak)
    If Len(Streak(X)) < Range("B1").Value Then Streak(X) = Replace(Streak(X), "1", "0")
  Next
  Streak = Split(StrConv(Trim(Join(Streak, "0")), vbUnicode), Chr(0))
  Range("B5").Resize(UBound(Streak)) = Application.Transpose(Streak)
End Sub

 

[sheetspread]

I'd go with VBA instead of formulas/conditional formatting here also. Either by adapting Rick's code, or perhaps something less fancy:

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

Sub hilite()
Dim i%, j%, lr%
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 + 1
Else
End If
Next
End Sub

 

[Peter_SSs]

Eventually, I would like the formula to be usable in Conditional Formatting and eliminate the need for the helper column.

OK, should be no need for vba. With standard Conditional Formatting
- Select A5:A??
- Conditional Formatting -> New rule.. -> Use a formula to determine which cells to format -> Format values where this formula is true: -> Apply the CF shown below, making sure the $1000 is larger than the last row of your data.
Altering the values in B1:B2 should immediately be reflected in the highlighting.

Excel Workbook
	A	B
1	streak	4
2	hurdle	20
3
4	Value
5	21
6	21
7	21
8	20
9	18.3
10	16.7
11	22.8
12	25
13	0
14	22.2
15	25
16	28.9
17	20
18	17.8
19	16.1
20	20
21	23.3
22	30
23	20
24	16.1
25	15.6
26	8.9
27	13.9
28	16.7
29	20
30	22.8
31	22.2
32	20.6
33	22.8
34	30
35
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=AGGREGATE(15,6,ROW(A5:A$1000)/(A5:A$1000=B$1))	text	NO

 

[DRSteele]

Peter, thank you very much very your reply. It's a very clever formula. Wow!

I wanted to avoid using the extra-row technique, primarily because the data is in an Excel Table. I figured you created it that way so that a NUM error is avoided when the final cells in the range all meet the criteria of being part of a 4-or-greater-length streak over 20. I toyed with the formula, and this is what I came up with. Have I done the right thing?

CSE:

=IFERROR(AGGREGATE(15,6,ROW(A5:A$34)/(A5:A$34 < B$2),1),ROW($A$34)+1)-IFERROR(AGGREGATE(14,6,ROW(A$4:A5)/(A$4:A5 < B$2),1),ROW(A$4))>=B$1<b$2),1),row($a$34)+1)-iferror(aggregate(14,6,row(a$4:a5) (a$4:a5<b$2),1),row(a$4))="">

Also, testing reveals there seems to be a problem with your formula. If you change B32 to something less than 20, the formula reports a true for the three cells B29, B30, B31. I notice that B33 is 20 or above, so maybe that has something to do with it.
</b$2),1),row($a$34)+1)-iferror(aggregate(14,6,row(a$4:a5)>

 

[Peter_SSs]

Also, testing reveals there seems to be a problem with your formula. If you change B32 to something less than 20, the formula reports a true for the three cells B29, B30, B31.

You are correct, the = sign near the end of my formula should be removed.
=AGGREGATE(15,6,ROW(A5:A$1000)/(A5:A$1000<B$2),1)-IFERROR(AGGREGATE(14,6,ROW(A$4:A5)/(A$4:A5&ly;B$2),1),ROW(A$4))>[highlight]=[/highlight]B$1

I figured you created it that way so that a NUM error is avoided when the final cells in the range all meet the criteria of being part of a 4-or-greater-length streak over 20.

Correct

I wanted to avoid using the extra-row technique, primarily because the data is in an Excel Table. I toyed with the formula, and this is what I came up with. Have I done the right thing?

No, that will not work. At least not if the table ever gets extended by adding new data at the end. In the existing table that ends on row 34, try making rows 32:34 above the hurdle and row 31 below. Your formula correctly does not highlight any of those last rows. Now add a new 'above hurdle number' in A35. The table automatically extends to row 35 and A32:A35 should now highlight, but they don't. In addition, I think you find no further rows in the table will get highlighted.

Is there a problem using the 'extra row' technique?


BTW, even if testing in worksheet cells, these AGGREGATE formulas do not require CSE entry.