Calculating Current Streak

[aimguest]

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?

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]L12 Week12[/TD]
[TD="class: xl65, width: 64"]L12 Week11[/TD]
[TD="class: xl65, width: 64"]L12 Week10[/TD]
[TD="class: xl65, width: 64"]L12 Week9[/TD]
[TD="class: xl65, width: 64"]L12 Week8[/TD]
[TD="class: xl65, width: 64"]L12 Week7[/TD]
[TD="class: xl65, width: 64"]L12 Week6[/TD]
[TD="class: xl65, width: 64"]L12 Week5[/TD]
[TD="class: xl65, width: 64"]L12 Week4[/TD]
[TD="class: xl65, width: 64"]L12 Week3[/TD]
[TD="class: xl65, width: 64"]L12 Week2[/TD]
[TD="class: xl65, width: 64"]L12 Week1[/TD]
[TD="class: xl65, width: 64"]Current Streak[/TD]
[/TR]
[TR]
[TD="class: xl66"]20.2%[/TD]
[TD="class: xl66"]11.4%[/TD]
[TD="class: xl66"]-5.5%[/TD]
[TD="class: xl66"]-7.0%[/TD]
[TD="class: xl66"]9.8%[/TD]
[TD="class: xl66"]-7.4%[/TD]
[TD="class: xl66"]4.9%[/TD]
[TD="class: xl66"]23.4%[/TD]
[TD="class: xl66"]4.6%[/TD]
[TD="class: xl66"]63.5%[/TD]
[TD="class: xl66"]27.7%[/TD]
[TD="class: xl66"]-5.3%[/TD]
[TD="class: xl67"]2 Win[/TD]
[/TR]
[TR]
[TD="class: xl66"]1.5%[/TD]
[TD="class: xl66"]14.2%[/TD]
[TD="class: xl66"]-5.3%[/TD]
[TD="class: xl66"]-5.9%[/TD]
[TD="class: xl66"]-3.9%[/TD]
[TD="class: xl66"]4.0%[/TD]
[TD="class: xl66"]1.4%[/TD]
[TD="class: xl66"]6.4%[/TD]
[TD="class: xl66"]-1.1%[/TD]
[TD="class: xl66"]0.9%[/TD]
[TD="class: xl66"]1.5%[/TD]
[TD="class: xl66"]-0.8%[/TD]
[TD="class: xl67"]2 Win[/TD]
[/TR]
[TR]
[TD="class: xl66"]-11.0%[/TD]
[TD="class: xl66"]10.5%[/TD]
[TD="class: xl66"]-17.5%[/TD]
[TD="class: xl66"]21.6%[/TD]
[TD="class: xl66"]-27.2%[/TD]
[TD="class: xl66"]1.4%[/TD]
[TD="class: xl66"]-14.7%[/TD]
[TD="class: xl66"]-14.1%[/TD]
[TD="class: xl66"]-4.0%[/TD]
[TD="class: xl66"]-8.6%[/TD]
[TD="class: xl66"]9.2%[/TD]
[TD="class: xl66"]-27.2%[/TD]
[TD="class: xl67"]1 Loss[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the Board.

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
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
2 win
2 win
1 loss

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]20.20%[/TD]
[TD="align: right"]11.40%[/TD]
[TD="align: right"]-5.50%[/TD]
[TD="align: right"]-7.00%[/TD]
[TD="align: right"]9.80%[/TD]
[TD="align: right"]-7.40%[/TD]
[TD="align: right"]4.90%[/TD]
[TD="align: right"]23.40%[/TD]
[TD="align: right"]4.60%[/TD]
[TD="align: right"]63.50%[/TD]
[TD="align: right"]27.70%[/TD]
[TD="align: right"]-5.30%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"]14.20%[/TD]
[TD="align: right"]-5.30%[/TD]
[TD="align: right"]-5.90%[/TD]
[TD="align: right"]-3.90%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]1.40%[/TD]
[TD="align: right"]6.40%[/TD]
[TD="align: right"]-1.10%[/TD]
[TD="align: right"]0.90%[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"]-0.80%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-11.00%[/TD]
[TD="align: right"]10.50%[/TD]
[TD="align: right"]-17.50%[/TD]
[TD="align: right"]21.60%[/TD]
[TD="align: right"]-27.20%[/TD]
[TD="align: right"]1.40%[/TD]
[TD="align: right"]-14.70%[/TD]
[TD="align: right"]-14.10%[/TD]
[TD="align: right"]-4.00%[/TD]
[TD="align: right"]-8.60%[/TD]
[TD="align: right"]9.20%[/TD]
[TD="align: right"]-27.20%[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]{=IFERROR(MATCH(0,IF(SIGN(A2)=SIGN(A2:L2),1,0),0)-1,COLUMNS(A2:L2))&IF(A2>0," win"," loss")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[aimguest]

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]

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:

=MIN(IF(SIGN(A2:L2)<>SIGN(A2),COLUMN(A2:L2)))-COLUMN(A2)&IF(A2>0," win"," loss")

with Control+Shift+Enter.

=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.

 

[aimguest]

Apologies for the lack of background. Appreciate your help!