Calculate Winning Streak in excel

M

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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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
 
Upvote 0
Is this what you want?
<br />
Book1
APAQARAS
2StreakWin/Loss
300
400
511
621
731
841
900
1011
1100
1211
1300
1400
1511
1621
1731
Sheet1
Cell Formulas
RangeFormula
AP3=IF(AS3=1,AP2+1,0)
 
Upvote 0
AlphaFrog said:
Is this what you want?

APAQARAS
StreakWin/Loss

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

</tbody>


Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10px, bgcolor: #E0E0F0"]AP3[/TH]
[TD="align: left"]=IF( AS3=1,AP2+1,0 )[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
Closer?

<br />
Book1
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
Sheet1
Cell Formulas
RangeFormula
AP3=IF(AS3>0,IF(AS2>0,AP2+1,1),IF(AS2=0,AP2-1,-1))
 
Upvote 0
AlphaFrog said:
Closer?


APAQARAS
StreakWin/Loss

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

</tbody>


Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10px, bgcolor: #E0E0F0"]AP3[/TH]
[TD="align: left"]=IF( AS3>0,IF(AS2>0,AP2+1,1),IF(AS2=0,AP2-1,-1) )[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Counting streak in a table column
Replies
3
Views
745
Eric W
Eric W
H
  • Question Question
Calculate longest unbeaten/winless streak in excel
Replies
8
Views
2K
HawkeyeBC
H
S
  • Solved
Calculate a winning streak per person
Replies
6
Views
2K
Peter_SSs
Peter_SSs
T
  • Solved
How to count a losing streak followed by winning streak
Replies
6
Views
439
Trie1977
T
D
  • Solved
Display streak of wins or losses
2
Replies
16
Views
932
dotcanada
D

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top