Implementing a streak counter for wins and losses in Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m working on a trading spreadsheet and I need help setting up a streak counter. Here’s what I’m trying to do:

I want a cell in my ‘Overall Stats’ sheet to keep track of winning and losing streaks. It should look at the ‘R’ (return) values in column Z of my ‘Trades’ sheet. When there are multiple positive Rs in a row, it should count that as a winning streak. Same goes for negative Rs and losing streaks.

For example:

A   B   C
R   R   R
+2  +1  -1

This would show a win streak of 2, then reset when it hits the negative value.

Can anyone help me figure out the formula for this? It would be super helpful to have both win and loss streaks tracked. Thanks in advance for any tips!

2

hey mike, i’ve got a simpler way. try this:

=ARRAYFORMULA(MAX(IF(Z:Z>0,COUNTIF(Z:Z&“>”,TRANSPOSE(Z:Z&“>”)),0)))

for win streaks. just flip the > to < for losses. it’s not perfect but gets the job done without the crazy formulas. lemme know if u need help!

3

I’ve tackled this issue before in my trading analytics. Here’s a streamlined approach that might work for you:

=ARRAYFORMULA(MAX(IF(Z:Z>0, SEQUENCE(ROWS(Z:Z))-VLOOKUP(SEQUENCE(ROWS(Z:Z)), IF(Z:Z<=0, ROW(Z:Z)), 1, 1), 0)))

This formula calculates the longest winning streak by looking at consecutive positive returns. For losing streaks, just change Z:Z>0 to Z:Z<0 and Z:Z<=0 to Z:Z>=0.

It’s more efficient than nested COUNTIF functions and works well with large datasets. The SEQUENCE function creates a running count, which is then offset by the most recent non-positive value using VLOOKUP.

Remember to adjust the column reference if your returns aren’t in column Z. Let me know if you need any clarification on implementation.

4

I’ve actually implemented something similar in my own trading spreadsheet, so I can share what worked for me. The key is using a combination of COUNTIF and MAX functions.

For win streaks, try this formula:

=MAX(ARRAYFORMULA(IF(Z:Z>0,COUNTIF(SPLIT(REPT(ROW(Z:Z)&“,”,LEN(Z:Z)-LEN(SUBSTITUTE(Z:Z,“+”,“”))+1),“,”),“<=”&ROW(Z:Z)),0)))

For loss streaks, just change the Z:Z>0 to Z:Z<0 and the “+” to “-”.

This looks at your entire column Z, counts consecutive positive or negative values, and returns the maximum streak. You’ll need to adjust the column reference if your returns aren’t in column Z.

It’s a bit complex, but it’s worked reliably for me. Let me know if you need any clarification on how it works!