How to calculate current win/loss/tie streak in Excel?

[smcmahon83]

Hello,


In column M, I have the following scoring system:

Win = 1
Tie = 0
Loss = -1

Can anyone recommend a formula, where I can calculate the current win/loss/tie streak? For example, if the last 4 results have all been losses, the formula would return -4 for the current streak. If the following game resulted in a tie(0), the streak would reset to 0, etc.

Thanks a million in advance to anyone who might be able to help here.

 

[Eric W]

Perhaps:

	A	B	C
1	Result		Streak
2	1		3 Win
3	1
4	1
5	0
6	-1
7	-1
8	1
9	0
10	1
11	0
12	-1
13	-1
14	-1
15	0
16	0
17	0
18	1
19	1
20	1
21

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =COUNTA(A:A)-LOOKUP(2,1/(($A$2:$A$100<>INDEX(A:A,COUNTA(A:A)))*($A$2:$A$100<>"")),ROW($A$2:$A$100))&CHOOSE(INDEX(A:A,COUNTA(A:A))+2," Loss"," Tie"," Win") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>