Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulatransposearray-formulas

Need help to create a formula in google spreadsheet

I Would like to create a formula to show in a cell the winning/loosing streak of a range if the number is positive or negative, and I would like to add the sum of the total value of the streak in a separate cell.

for example I have a basic table with

A1:A100 I have different number positive or negative and in B1 I want to show the longest positive streak of the range, let's say is from A25 to A45 so the value should be 10, and in B2 I want to show the total value of the sum of A25 to A45. In C1 and C2 the same but for negative

EDIT: I would like also To show the highest sum of negative/positive of any streak in the range not especially the longest

I hope is clear enough.

Solution

  • enter image description here

    D1:

    =ARRAYFORMULA(MAX(LEN(SPLIT(JOIN(, IF(A:A>0, 1, 0)), 0))))

    D2:

    =ARRAYFORMULA(MAX(LEN(SPLIT(JOIN(, IF(A:A<0, 1, 0)), 0))))

    D3:

    =ARRAYFORMULA(SUM(SPLIT(INDEX(SORT(TRANSPOSE({LEN(SPLIT(JOIN(, 
 IF(A:A>0, 1, "♥")), "♥")); SPLIT(JOIN(, 
 IF(A:A>0, A:A&"♦", "♥")), "♥")}), 1, 0), 1, 2), "♦")))

    D4:

    =ARRAYFORMULA(SUM(SPLIT(INDEX(SORT(TRANSPOSE({LEN(SPLIT(JOIN(, 
 IF(A:A<0, 1, "♥")), "♥")); SPLIT(JOIN(, 
 IF(A:A<0, A:A&"♦", "♥")), "♥")}), 1, 0), 1, 2), "♦")))