Trend formula- consecutive positive and negative numbers

Hi all,
I have a large data set with dates in column A and the daily price percentage change of an asset in column B. Over the last 10 days, the price of the asset has fallen and I was wondering whether there is a formula of determining when the last time the pattern occurred?
I'm having to do it manually and I'm going out of my mind. Any help would be utterly appreciated

Here is my data set.

Hi,
I'm looking to see when the last time before June 3 when prices fell for 10 consecutive sessions, is this possible?

Originally Posted by Col83

I have a large data set with dates in column A and the daily price percentage change of an asset in column B. Over the last 10 days, the price of the asset has fallen and I was wondering whether there is a formula of determining when the last time the pattern occurred?

If you are amenable to a VBA solution, try the following procedure. It finds the dates of all runs that greater than or equal to the goal (10).

In Excel, right-click on the worksheet tab at the bottom, then click on View Code. Copy the text of the procedure below, and paste into the VBA edit on the right.

You can close the VBA window. It would be prudent to save the Excel workbook as macro-enabled (xlsm).

In Excel, press alt-F8 and double-click on "findit" to execute the macro.

Sub findit()
    ' Assumes data are in column A (date) and B (%change).
    ' Select location for result.
    ' Results:  1st column is date; 2nd column is run length
    
    Const nGoal As Long = 10
    Dim v As Variant, nV As Long
    Dim nRun As Long, nRun0 As Long
    Dim i As Long, nRes As Long
    
    ' input data
    v = Range("a1", Cells(Rows.Count, "b").End(xlUp))
    nV = UBound(v, 1)
    
    ' find dates when run of -%change >= nGoal
    ReDim res(1 To nV, 1 To 2) As Variant
    nRun = 0: nRes = 0
    For i = 1 To UBound(v, 1)
        nRun0 = nRun
        If Not WorksheetFunction.IsNumber(v(i, 2)) Then nRun = 0 _
        Else If v(i, 2) >= 0 Then nRun = 0 _
        Else nRun = nRun + 1
        If nRun = 0 And nRun0 >= nGoal Then
            nRes = nRes + 1
            res(nRes, 1) = v(i - 1, 1)
            res(nRes, 2) = nRun0
        End If
    Next
    ' in case end of data is run of -%change
    If nRun >= nGoal Then
        nRes = nRes + 1
        res(nRes, 1) = v(nV, 1)
        res(nRes, 2) = nRun
    End If
    
    ' output results
    Selection.Resize(nRes, 2) = res
    MsgBox "done"
End Sub