+ Reply to Thread
Results 1 to 10 of 10

Identifying Current Streaks of Goals/Points

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    Powell River, BC
    MS-Off Ver
    Excel 2007
    Posts
    32

    Identifying Current Streaks of Goals/Points

    What I'm asking is somewhat similar to what was discussed here: http://www.excelforum.com/excel-gene...ss-streak.html

    I have a work book which tracks stats for a hockey team I work for. Each player has their own page with game logs, tracking goals, goal types, penalties and other hockey stats. I'm looking to calculate goal and point streaks off this.

    I've included an example of what I'm looking for.
    streak example.xlsx

    I want to clarify that I'm not looking for the longest streak. Using the above example, if it's October 13th and this player gets 1 assist, then the goal streak goes to 0, the assist streak goes to 2 and the point streak goes to 6.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Identifying Current Streaks of Goals/Points

    Hi alex,

    See the attached file, yellow cell.
    For other two calculation, I am not clear - please provide more details. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    02-20-2012
    Location
    Powell River, BC
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Identifying Current Streaks of Goals/Points

    Hi DILIPandy

    We're on the right track, but not quite what I'm looking for. Your calculation attached above shows the highest streak in a given range, not the 'current' streak.

    In column C, I'm looking for the current streak, ie: consecutive numbers >1 from the bottom up. If I add '1' to C10, your value in I4 goes to 5, which is correct. However, if I add '0' in C10, then the value in I4 stays at 4, but it should be '0'.

    In terms of your question, the other two categories are the same calculation, just related to columns D and E respectively. Once I can get one working, the others will be just copies.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Identifying Current Streaks of Goals/Points

    Ok.. so let me rephrase your query:-

    You want the sum of consecutive 1s, no matter if they are 4 in count or more than that... but if there is 0, then the answer should be 0.
    Please confirm.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    02-20-2012
    Location
    Powell River, BC
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Identifying Current Streaks of Goals/Points

    Correct, but not limited to '1', but '>=1'. So if C2=1 and C3=2, then I4=2. Then later, if C4=0, then I4=0. Then later if C5=1, C6=1, C7=1, then I4=3. Looking for the consecutive number of values >=1 from the bottom of the list up.

  6. #6
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Identifying Current Streaks of Goals/Points

    Hi

    Try this
    Cell I4 =MAX(FREQUENCY(IF(C2:C10<>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))) confirmed with CONTROL+SHIFT+ENTER
    Cell I5 =MAX(FREQUENCY(IF(D2:D10<>0,ROW(D2:D10)),IF(D2:D10=0,ROW(D2:D10)))) confirmed with CONTROL+SHIFT+ENTER
    Cell I6 =MAX(FREQUENCY(IF(E2:E10<>0,ROW(E2:E10)),IF(E2:E10=0,ROW(E2:E10)))) confirmed with CONTROL+SHIFT+ENTER

    Good luck.
    Last edited by micope21; 04-04-2012 at 05:18 AM.

  7. #7
    Registered User
    Join Date
    02-20-2012
    Location
    Powell River, BC
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Identifying Current Streaks of Goals/Points

    Quote Originally Posted by micope21 View Post
    Hi

    Try this
    Cell I4 =MAX(FREQUENCY(IF(C2:C10<>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))) confirmed with CONTROL+SHIFT+ENTER
    Cell I5 =MAX(FREQUENCY(IF(D2:D10<>0,ROW(D2:D10)),IF(D2:D10=0,ROW(D2:D10)))) confirmed with CONTROL+SHIFT+ENTER
    Cell I6 =MAX(FREQUENCY(IF(E2:E10<>0,ROW(E2:E10)),IF(E2:E10=0,ROW(E2:E10)))) confirmed with CONTROL+SHIFT+ENTER

    Good luck.
    Hi Micope

    Thanks for your effort, but again it's not quite what I'm looking for. Your formula shows that max streak through a range, however when C10=0, thus the streak of >=0 is broken, the result in I4 should be 0, not 4 (the max in the range).

  8. #8
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Identifying Current Streaks of Goals/Points

    Ok
    This one will work.
    Click on attachment
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-20-2012
    Location
    Powell River, BC
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Identifying Current Streaks of Goals/Points

    Massive followup bump!

    Is there a way to reverse this? To show slumps instead of streaks? Essentially for looking for consecutive zero's, instead of consecutive >=1.

    This is the formula that provides my streaks.

    =OFFSET('2 SMITH'!F3,COUNT('2 SMITH'!F4:F59),0,1,1)
    Last edited by alexrawnsley; 10-23-2012 at 09:41 PM.

  10. #10
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Identifying Current Streaks of Goals/Points

    Hi
    Could you upload your workbook?
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1