I do like an array formula, they do away with the use of VBA...
I have been busy all weekend but feel like you guys are waiting for me to have a play with this. I have had a go with no macro's at all nor any power query. I think I have answered both questions in the attached file.
I have:
Converted your range A:E into a table object.
Added an array formula to cell AC2
Added an array formula to cell AK2
Added an array formula to cell AN1
Added an array formula to cell I37
Short explanation of the formulae:
AC2:
=LET(
t,LET(d,--INDEX(TEXTSPLIT(G34,," "),2),t,Table2,FILTER(t,(INDEX(t,,1)<=TODAY())*(INDEX(t,,1)>=TODAY()-(d-1)))),
ops,IF(INDEX(t,,2)>=G37,G37,NA()),
scan1,SCAN(0,ops,LAMBDA(x,a,IF(NOT(ISNA(a)),x,x+1))),
scan2,SCAN(0,ops,LAMBDA(x,a,IF(NOT(ISNA(a)),x+1,0))),
plot,IF(MAXIFS(scan2,scan1,scan1)>2,G37,NA()),
HSTACK(t,ops,scan1,scan2))
This formula mimics your data in A:E and adds a few columns that allow me to filter out the dates for the opportunities data in cell I37. This formula expands with the table that was created in A:E.
AK2:
=IF(INDEX(AC2#,,8)>0,IF(MAXIFS(INDEX(AC2#,,8),INDEX(AC2#,,7),INDEX(AC2#,,7))>2,G37,NA()),NA())
This formula finds the opportunities and places the value in cell G37 if it finds one. This formula will auto expand as it feeds off of the array formula in cell AC2.
AN1:
=VSTACK(AC1:AK1,HSTACK(AC2#,AK2#))
This formula turns the headers AC1:AK1 as well as both AC2 & AK2 array formulae into one single array formula. I have done this as the chart you have will then expand itself to fit this array formula. So the formula in AN1 feeds the chart.
I37:
=LET(
t,SORT(CHOOSECOLS(FILTER(AC2#,NOT(ISNA(INDEX(AC2#,,6)))),1,7,8),{2,1},{1,-1}),
i,UNIQUE(INDEX(t,,2)),
tt,HSTACK(XLOOKUP(i,INDEX(t,,2),INDEX(t,,1)),XLOOKUP(i,INDEX(t,,2),INDEX(t,,3))),
d,CHOOSECOLS(FILTER(tt,INDEX(tt,,2)>2),2,1),
TEXT(INDEX(d,,2)-(INDEX(d,,1)-1),"dd/mm/yyyy") & " > " & TEXT(INDEX(d,,2),"dd/mm/yyyy"))
This formula takes the max count of each opportunity along with the max date of each opportunity. It then creates two dates by taking away the max count from the max date. This then gives us a start date and end date for each opportunity. It takes those two dates and joins them together as a text value separated by a > symbol.
There may be other ways to do this or I think an opportunity to simplify the formula, I may look at this soon but for now I have run out of time.
For now, the whole thing is based on the value being greater than the value you place in cell G37, there is no buffer it has to be greater than. Also, it looks for any opportunities that are greater or equal to 3 days rather than just it being 3 in a row.
Let me know what you think?