Calculating Current Streak

aimguest

New Member
Joined
Jun 20, 2018
Messages
3
Hello,

I'm looking to calculate the current streak of either above 0% or below 0%. Here are a few lines of my data below and in the right most column I'd like for the cell to output the number of consecutive wins/losses. The streak needs to end with the left most column (L12 Week12).

Can anyone help me with this formula?

L12 Week12L12 Week11L12 Week10L12 Week9L12 Week8L12 Week7L12 Week6L12 Week5L12 Week4L12 Week3L12 Week2L12 Week1Current Streak
20.2%11.4%-5.5%-7.0%9.8%-7.4%4.9%23.4%4.6%63.5%27.7%-5.3%2 Win
1.5%14.2%-5.3%-5.9%-3.9%4.0%1.4%6.4%-1.1%0.9%1.5%-0.8%2 Win
-11.0%10.5%-17.5%21.6%-27.2%1.4%-14.7%-14.1%-4.0%-8.6%9.2%-27.2%1 Loss

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Welcome to the Board.

Try:


ABCDEFGHIJKLM
1L12 Week12L12 Week11L12 Week10L12 Week9L12 Week8L12 Week7L12 Week6L12 Week5L12 Week4L12 Week3L12 Week2L12 Week1Current Streak
220.20%11.40%-5.50%-7.00%9.80%-7.40%4.90%23.40%4.60%63.50%27.70%-5.30%2 win
31.50%14.20%-5.30%-5.90%-3.90%4.00%1.40%6.40%-1.10%0.90%1.50%-0.80%2 win
4-11.00%10.50%-17.50%21.60%-27.20%1.40%-14.70%-14.10%-4.00%-8.60%9.20%-27.20%1 loss

<tbody>
</tbody>


Array Formulas
CellFormula
M2{=IFERROR(MATCH(0,IF(SIGN(A2)=SIGN(A2:L2),1,0),0)-1,COLUMNS(A2:L2))&IF(A2>0," win"," loss")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

aimguest

New Member
Joined
Jun 20, 2018
Messages
3
Eric,

Thanks for the welcome and the quick reply! The formula works well, however, I'm uploading this sheet to a TM1 Website and that does not support the Match or Iferror functions. Do you have any other ways of calculating the same output?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
That's the kind of information that would have been useful to know. In the future, please try to include such information to help anyone trying to help you.

I don't know anything about TM1 and what functions it has or has not available, and I'm really not going to try to guess. However, here are 2 formulas I experimented with before posting the one I did:

Code:
=MIN(IF(SIGN(A2:L2)<>SIGN(A2),COLUMN(A2:L2)))-COLUMN(A2)&IF(A2>0," win"," loss")
with Control+Shift+Enter.

Code:
=INDEX(FREQUENCY(IF(SIGN(A2:L2)=SIGN(A2),COLUMN(A2:L2)),IF(SIGN(A2:L2)<>SIGN(A2),COLUMN(A2:L2))),1)&IF(A2>0," win"," loss")
with Control+Shift+Enter.

Both of these fail if you have a streak of 12, which is why I chose the one I did. Hopefully you'll find one that works.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,270
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top