Conditional Sums

[pstrickland]

I have a spreadsheet that represents stock daily data and associated trades in rows. As with all trading, some trades are winners and some losers and on some days, there are no trades, as represented by "0". Winners are identified in a column by a "1" and losers by a "-1". I need a formula that will sum the largest number of consecutive winners and losers.

[DonkeyOte]

You can use an Array

Please Login or Register  to view this content.

Modify ranges as nec. but try to keep as lean as possible

above assumes 0's (like -1's) stop the winning streak ... for losing streak change references to 1 to -1

[pstrickland]

What if the column values look something like 1,0,0,1,0,0,1,1? Since the "0" stops the streak this particular formula will not work.

Thanks for the help.

[DonkeyOte]

For a data set of:

1,0,0,1,0,0,1,1

the prior formula returns 2.

Since the "0" stops the streak this particular formula will not work.

The above remains unclear... if 0 does not stop the streak then modify accordingly:

Please Login or Register  to view this content.

The above would return 8 - if you expect result of 4: ie 0 does not stop streak but does not count towards it, then:

Please Login or Register  to view this content.