How to sum longest consecutive streak of positive/negative values

1
votes

In range A1:A100, there are dates. In range B1:B100, there are positive and negative numbers. 1/2/2019 -5
1/3/2019 -51
1/4/2019 100
1/7/2019 27
1/8/2019 -35
1/9/2019 52
1/10/2019 26
1/11/2019 19
1/12/2019 -42
1/13/2019 -213
1/14/2019 -79

Now, I have figured out how to find the longest consecutive streak of positive and negative values. However, I would also like to find the sum of those streaks. Ideally, I would like to not use helper columns. (I apologize for the poor table formatting.)

I found a solution that works in Excel but for some reason fails in Google Sheets, available here: https://www.mrexcel.com/forum/excel-questions/330468-sum-longest-streak.html The provided formula when copied to Google Sheets results in an error stating that there is no valid input for the small() function. I've compared Excel to Google Sheets side by side and the formula is successful in Excel but not Sheets.

Positive streak: ArrayFormula(MAX(FREQUENCY(IF(ISNUMBER(B:B),IF(B:B>=0,ROW(B:B))),IF(B:B<0,ROW(B:B)))))

Negative streak: ArrayFormula(MAX(FREQUENCY(IF(B1:B99<0,A1:A99),IF(B1:B99>=0,A1:A99))))

There may be more than 1 longest streak. I would like to return the one with the greatest sum, if possible.

The positive streak sum should be 97 and the negative streak should be -334.

1
google-sheets-formula

1 Answers

0
votes

It seems the Excel solution you appreciate uses helper columns so, insert a new Row 1 and in C2:

=if(and($B2>0,sign($B1)=sign($B2)),C1+1,1)

Copy across to D2, change > to < there and copy C2:D2 down to suit. Then:

=sum(offset(indirect("C"&match(max(C:C),C:C,0)),1-max(C:C),-1,max(C:C),1))

and copy across to the right.

This work for your sample but does not find the highest/lowest sum for streaks of equal length.