Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Calculate Winning Streak in excel

  1. #1
    New Member
    Join Date
    Jun 2012
    Location
    NH, USA
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate Winning Streak in excel

    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.

  2. #2
    New Member
    Join Date
    Jun 2012
    Location
    NH, USA
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    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

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,942
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Calculate Winning Streak in excel

    Is this what you want?

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


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

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  4. #4
    New Member
    Join Date
    Jun 2012
    Location
    NH, USA
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    Quote Originally Posted by AlphaFrog View Post
    Is this what you want?

    AP AQ AR AS
    2 Streak Win/Loss
    3 0 0
    4 0 0
    5 1 1
    6 2 1
    7 3 1
    8 4 1
    9 0 0
    10 1 1
    11 0 0
    12 1 1
    13 0 0
    14 0 0
    15 1 1
    16 2 1
    17 3 1


    Worksheet Formulas
    Cell Formula
    AP3 =IF(AS3=1,AP2+1,0)
    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.

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,942
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Calculate Winning Streak in excel

    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


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

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  6. #6
    New Member
    Join Date
    Jun 2012
    Location
    NH, USA
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    Quote Originally Posted by AlphaFrog View Post
    Closer?


    AP AQ AR AS
    2 Streak Win/Loss
    3 -1 0
    4 -2 0
    5 -3 0
    6 1 1
    7 2 1
    8 3 1
    9 -1 0
    10 1 1
    11 -1 0
    12 1 1
    13 -1 0
    14 -2 0
    15 1 1
    16 2 1
    17 3 1


    Worksheet Formulas
    Cell Formula
    AP3 =IF(AS3>0,IF(AS2>0,AP2+1,1),IF(AS2=0,AP2-1,-1))
    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.

  7. #7
    New Member
    Join Date
    Jun 2012
    Location
    NH, USA
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    I figured it out, thank you for the base formula!

  8. #8
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    Quote Originally Posted by mailbox View Post
    I figured it out, thank you for the base formula!
    How did you do that?

  9. #9
    New Member
    Join Date
    Apr 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate Winning Streak in excel

    I know this post is 6 years old. Can you answer how you did that. Please and thank you.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •