Identify rows where value exceeds hurdle consecutively

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,639
Office Version
  1. 365
Platform
  1. Windows
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
AB
1streak4
2hurdle20
3
4ValuePart of Streak?
521.01
621.01
721.01
820.01
918.30
1016.70
1122.80
1225.00
130.00
1422.21
1525.01
1628.91
1720.01
1817.80
1916.10
2020.01
2123.31
2230.01
2320.01
2416.10
2515.60
268.90
2713.90
2816.70
2920.01
3022.81
3122.21
3220.61
3322.81
3430.01
Sheet18
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Here's a CF rule that highlights the first cell of each group of (or whatever is in B1).
Excel Workbook
AB
1streak4
2hurdle20
3
4Value
521
621
721
820
918.3
1016.7
1122.8
1225
130
1422.2
1525
1628.9
1720
1817.8
1916.1
2020
2123.3
2230
2320
2416.1
2515.6
268.9
2713.9
2816.7
2920
3022.8
3122.2
3220.6
3322.8
3430
Sheet1


The rule:
Code:
=AND($A5>=$B$2,COUNTIF(OFFSET($A5,0,0,$B$1,1),">="&$B$2)=$B$1)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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...
Code:
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
 
Last edited by a moderator:
Upvote 0
I'd go with VBA instead of formulas/conditional formatting here also. Either by adapting Rick's code, or perhaps something less fancy:

Book1
AB
1streak4
2hurdle20
3
4ValuePart of Streak?
5211
6211
7211
8201
918.30
1016.70
1122.80
12250
1300
1422.21
15251
1628.91
17201
1817.80
1916.10
20201
2123.31
22301
23201
2416.10
2515.60
268.90
2713.90
2816.70
29201
3022.81
3122.21
3220.61
3322.81
34301
Sheet14


Code:
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
 
Last edited by a moderator:
Upvote 0
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
AB
1streak4
2hurdle20
3
4Value
521
621
721
820
918.3
1016.7
1122.8
1225
130
1422.2
1525
1628.9
1720
1817.8
1916.1
2020
2123.3
2230
2320
2416.1
2515.6
268.9
2713.9
2816.7
2920
3022.8
3122.2
3220.6
3322.8
3430
35
CF

Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5Expression=AGGREGATE(15,6,ROW(A5:A$1000)/(A5:A$1000=B$1))textNO
 
Last edited by a moderator:
Upvote 0
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:
Code:
=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)>
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top