Formula help: Identify specific sequences of numbers in a column

[JackDanIce]

Hi,

I have a column of numbers with integer values: -1, 0 and 1, which represents if a game is lost, drawn or won.

If there is a sequence of non-positive values that sum to -3, I want the formula to return 0.
After this, the formula should return 1 if and only if there is a sequence of non-negative values that sum to 2

This is an example of the match results and formula output next to it:

Match Result	Formula result
-1	1
0	1
0	1
-1	1
-1	0
-1	0
0	0
1	0
-1	0
1	0
0	0
1	1

Can anyone suggest a suitable formula please?

TIA,
Jack

 

[JackDanIce]

Thank you, I'd prefer a formula but worse case I'll be able to figure some code out

 

[johnnyL]

The following is what I have come up with for a custom function that will allow you to use formulas on the sheet:

Book5
	A	B	C	D	E	F	G
1	Match Result	Formula result	Match Result	Formula result	Match Result	Formula result
2	-1	Yes	1	Yes	0	Yes
3	-1	Yes	1	Yes	1	Yes
4	0	Yes	1	Yes	-1	Yes
5	1	Yes	1	Yes	-1	Yes
6	0	Yes	1	Yes	-1	Yes
7	-1	Yes	1	Yes	-1	Yes
8	-1	Yes	1	Yes	-1	Yes
9	-1	Yes	-1	Yes	-1	No
10	-1	Yes	-1	Yes	0	No
11	1	Yes	-1	Yes	1	No
12	0	Yes	-1	Yes	0	No
13	1	Yes	-1	Yes	1	No
14	0	Yes	1	No	1	Yes
15			0	No	-1	Yes
16			1	No	-1	Yes
17			0	Yes	-1	Yes
18
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=SequenceTracker(A2:A14)
F2:F17,D2:D17	D2	=SequenceTracker(C2:C17)
Press CTRL+SHIFT+ENTER to enter array formulas.

The following is the function code to be copied into a regular module:

Function SequenceTracker(rng As Range) As Variant
'
    Dim ArrayRow            As Long
    Dim CurrentMatchResult  As Long
    Dim SequenceSum         As Long
    Dim SequenceType        As String
    Dim BetArray()          As Variant, MatchResultArray    As Variant
'
    MatchResultArray = rng.Value                                                        ' Load the Input range of data into MatchResultArray
'
    ReDim BetArray(1 To UBound(MatchResultArray, 1), 1 To 1)                            ' Establish the dimensions of the BetArray
'
    SequenceSum = 0                                                                     ' Initialize SequenceSum
    SequenceType = ""                                                                   ' Initialize SequenceType
   
    For ArrayRow = LBound(MatchResultArray, 1) To UBound(MatchResultArray, 1)           ' Loop through the rows of MatchResultArray
        CurrentMatchResult = MatchResultArray(ArrayRow, 1)                              '   Get the value of the CurrentMatchResult
'
'
'
        If SequenceType = "LostOrTied" Then                                             '   Else if SequenceType = "LostOrTied" Then ...
            If CurrentMatchResult = -1 Or CurrentMatchResult = 0 Then                   '       If current input cell value = -1 or 0 then ...
                If SequenceSum <= -5 Then                                                '           If SequenceSum <= -5 Then ...
                    BetArray(ArrayRow, 1) = "No"                                        '               Save 'No' to BetArray
                Else                                                                    '           Else
                    BetArray(ArrayRow, 1) = "Yes"                                       '               Save 'Yes' to BetArray
                End If
'
                SequenceSum = SequenceSum + CurrentMatchResult                          '           Add current input cell value to SequenceSum
            Else                                                                        '       Else ...
                If CurrentMatchResult = 1 Then                                          '           If current input cell value = 1 then ...
                    If SequenceSum <= -5 Then                                           '               If SequenceSum <= -5 Then ...
                        BetArray(ArrayRow, 1) = "No"                                    '                   Save 'No' to BetArray
                    Else                                                                '               Else ...
                        BetArray(ArrayRow, 1) = "Yes"                                   '                   Save 'Yes' to BetArray
                    End If
'
                    SequenceType = "WonOrTied"                                          '               Set SequenceType to 'WonOrTied'
                    SequenceSum = CurrentMatchResult                                    '               Save current input cell value to SequenceSum
                End If
            End If
'
'
'
        ElseIf SequenceType = "WonOrTied" Then                                          '   Else if SequenceType = "WonOrTied" then ...
            If CurrentMatchResult = 0 Or CurrentMatchResult = 1 Then                    '       If current input cell value = 0 or 1 then ...
                If SequenceSum >= 2 Then                                                 '           If SequenceSum => 2 Then ...
                    BetArray(ArrayRow, 1) = "Yes"                                       '               Save 'Yes' to BetArray
                Else                                                                    '           Else ...
                    BetArray(ArrayRow, 1) = BetArray(ArrayRow - 1, 1)                   '               Save previous result to BetArray
                End If
'
                SequenceSum = SequenceSum + CurrentMatchResult                          '           Add current input cell value to SequenceSum

            ElseIf CurrentMatchResult = -1 Then                                         '       Else If current input cell value = -1 then...
                    BetArray(ArrayRow, 1) = BetArray(ArrayRow - 1, 1)                   '           Save previous result to BetArray
                    SequenceType = "LostOrTied"                                         '           Set SequenceType to 'LostOrTied'
                    SequenceSum = CurrentMatchResult                                    '           Save current input cell value to SequenceSum
            End If
'
'
'
        ElseIf SequenceType = "" Then                                                   '   If SequenceType hasn't been set then ...
            If CurrentMatchResult = -1 Or CurrentMatchResult = 0 Then                   '       If current input cell value = -1 or 0 then ...
                SequenceType = "LostOrTied"                                             '           Set SequenceType to 'LostOrTied'
                SequenceSum = CurrentMatchResult                                        '           Save current input cell value to SequenceSum
            Else                                                                        '       Else ...
                If CurrentMatchResult = 1 Then                                          '           If current input cell value = 1 then ...
                    SequenceType = "WonOrTied"                                          '               Set SequenceType to 'WonOrTied'
                    SequenceSum = CurrentMatchResult                                    '               Save current input cell value to SequenceSum
                End If
            End If
'
            BetArray(ArrayRow, 1) = "Yes"                                               '       Save 'Yes' to BetArray
        End If
    Next                                                                                ' Loop back
'
    SequenceTracker = BetArray                                                          '
End Function

 

[JackDanIce]

Thank you, I'll take a more detailed look at the code, but on initial glance, I may be able to convert it into a LAMBDA driven function with three input arguments for the range of values, X for -5 (non positive or "loss and draw" results") and Y for 2 (non negative or "win and draw results"), i.e. to increase flexibility, but appreciate the effort and inspiration

I'll post anything if I manage to create as it would then still satisfy having a non .xlsm version.

Cheers @johnnyL!

 

[johnnyL]

Thank you @JackDanIce! I wish I could help more with a possible formula approach, but I am not that good with the formula side of Excel

 

[JackDanIce]

Thank you @JackDanIce! I wish I could help more with a possible formula approach, but I am not that good with the formula side of Excel

 

[JackDanIce]

This is a formula used in the same spreadsheet the above ask relates to and it's effectively an algorithm proposed within a white paper for some data analysis I'm working on, but I managed to create a single function for it, even though broken down, it requires an input date, fixed variable (lag) and 4 "flag" variables, used to sum or average rows of data into a final value - previously needed 4 calculating columns, now just an output column

=LET(
    lag, INDEX(flag, 1, 2) - 1,
    rng, LAMBDA(a,
        OFFSET(XLOOKUP(xDate - a - lag, input[Date], input[ABC Calc], 0, 0, 1), 0, 0, a)
    ),
    REDUCE(
        0,
        SEQUENCE(ROWS(flag) - 1, , 2),
        LAMBDA(n, b,
            n +
                CHOOSE(
                    1 + --(b > 3),
                    IFERROR(SUM(rng(INDEX(flag, b, 2))), 0),
                    IFERROR(AVERAGE(rng(INDEX(flag, b, 2))), 0)
                )
        )
    )
)

For what I asked here, it's needing to have a temporary store for the accumulation of loses/draws and then resetting it back to 0 each time there's a win or vice versa I was getting stuck on, hence posting to the board for help But scanning your code, I think I might be able to convert now aka given me a starting point