# Calculating Current Streak

#### aimguest

##### New Member
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 Week12 L12 Week11 L12 Week10 L12 Week9 L12 Week8 L12 Week7 L12 Week6 L12 Week5 L12 Week4 L12 Week3 L12 Week2 L12 Week1 Current 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>

### 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
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
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
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:

#### aimguest

##### New Member
Apologies for the lack of background. Appreciate your help!

Replies
4
Views
39
Replies
10
Views
96
Replies
0
Views
197
Replies
2
Views
225