Calculate Winning Streak in excel

mailbox

New Member
Joined
Jun 30, 2012
Messages
7
Hello,

I am working in Excel 2010. I am trying to write a formula to calculate a team's current winning/losing streak. I have wins and losses each in their own column, represented by a 1 or a 0. I would like to represent the current streak in a single column where (+) values represent a winning streak and (-) values represent a losing streak.

I have tried multiple combinations of formulas, with varying success. My current formula is: =IF(AS4="1",AP4+1,AP4-1). This works for a continuing winning or losing streak, but the issue arises when a team breaks a streak. For example if they win then lose, or vice versa.


Any help is greatly appreciated. I uploaded my spreadsheet to Google docs here.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mailbox

New Member
Joined
Jun 30, 2012
Messages
7
One additional note: I am using random numbers to simulate the results of the first 2 weeks. This is the reason for the #N/A results. I am not worried about these, I am only concerned about the streak formula at this moment
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
Is this what you want?
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>AP</th><th>AQ</th><th>AR</th><th>AS</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Streak</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Win/Loss</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AP3</th><td style="text-align:left">=IF(<font color="Blue">AS3=1,AP2+1,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

mailbox

New Member
Joined
Jun 30, 2012
Messages
7
Is this what you want?

APAQARAS
2StreakWin/Loss
300
400
511
621
731
841
900
1011
1100
1211
1300
1400
1511
1621
1731

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


Worksheet Formulas
CellFormula
AP3=IF(AS3=1,AP2+1,0)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Not exactly. I would like losing streaks to be represented as well. So instead of a 0 for a losing streak I am trying to have it display -1, -2 etc.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
Closer?

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>AP</th><th>AQ</th><th>AR</th><th>AS</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Streak</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Win/Loss</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AP3</th><td style="text-align:left">=IF(<font color="Blue">AS3>0,IF(<font color="Red">AS2>0,AP2+1,1</font>),IF(<font color="Red">AS2=0,AP2-1,-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

mailbox

New Member
Joined
Jun 30, 2012
Messages
7
Closer?


APAQARAS
2StreakWin/Loss
3-10
4-20
5-30
611
721
831
9-10
1011
11-10
1211
13-10
14-20
1511
1621
1731

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


Worksheet Formulas
CellFormula
AP3=IF(AS3>0,IF(AS2>0,AP2+1,1),IF(AS2=0,AP2-1,-1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
it works going down the column, but the way i have my data set up each team has its own row, I would like the streak for each team, going across the row, for each week. The formula works for each week, but not for an individual team. If that makes any sense.
 

mailbox

New Member
Joined
Jun 30, 2012
Messages
7
I figured it out, thank you for the base formula!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,806
Messages
5,446,575
Members
405,409
Latest member
croc23

This Week's Hot Topics

Top