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?

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>

 

[Eric W]

Welcome to the Board.

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	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	20.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
3	1.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 Cell Formula 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]

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!