I want be able to calculate the total number of winning & losing streaks in an array on google sheets.

If I calculate if manually on the below example, I get a total of 5 streaks. Is there a way to do this with functions?

CodePudding user response：

try:

=LAMBDA(x, y, {x, y, x y})(
 COUNTA(SPLIT(TEXTJOIN(, 1, A1:A), "l")), 
 COUNTA(SPLIT(TEXTJOIN(, 1, A1:A), "w")))

CodePudding user response：

To ignore single wins and losses and only count streaks of at least two consecutive wins or losses, use player0's formula where you replace counta() with query(), like this:

=lambda( 
  range, winTag, lossTag, 
  lambda( 
    numWins, numLosses, 
    { numWins, numLosses, numWins   numLosses } 
  )(
    query( transpose( split(textjoin("", true, range), lossTag) ), "select count(Col1) where Col1 <> '" & winTag & "' label count(Col1) '' ", 0 ), 
    query( transpose( split(textjoin("", true, range), winTag) ), "select count(Col1) where Col1 <> '" & lossTag & "' label count(Col1) '' ", 0 ) 
  )
)(A1:A, "w", "l")

CodePudding user response：

Input:

Formula:

=REDUCE(
  {"Streak","Count"},
  {"w","l","Total"}, 
  LAMBDA(
    a,c,
    {
      a;
      c,LEN(
        REGEXREPLACE(
          REGEXREPLACE(
            JOIN(,A2:A10),
            IF(c="Total","l{2,}|w",c)&"{2,}",
            "∞"
          ),
        "[^∞]",)
      )
    }
  )
)

JOIN the array(wwwllwlww). Use REGEXREPLACE to replace two or more sequences of w with a delimiter(∞llwl∞). On the resulting string, use REGEXREPLACE again to remove everything except the delimiter(∞∞). The length of the remaining string is the streak. Use REDUCE to loop over for other items like loss

Output: