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

smcmahon83

Board Regular
Joined
Jul 12, 2014
Messages
58
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Perhaps:

ABC
1ResultStreak
213 Win
31
41
50
6-1
7-1
81
90
101
110
12-1
13-1
14-1
150
160
170
181
191
201
21

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

Worksheet Formulas
CellFormula
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,223
Members
409,688
Latest member
Mc Junior

This Week's Hot Topics

Top