+ Reply to Thread
Results 1 to 8 of 8

Exclude zero when counting streaks (win/loss) and current streaks

  1. #1
    Registered User
    Join Date
    10-26-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Exclude zero when counting streaks (win/loss) and current streaks

    Hi,

    I had been trying to figure out calculating streaks (win/loss) for my football's team statistics and is facing the following 2 issues.

    My example:

    Column: A B C D E F G H I J K L M N O P Q R
    Row 1: w w w L 0 L 0 L 0 w w w 0 0 w L 0 L

    0 denotes non-appearance and thus should not be considered in the calculation.

    1) To find the current streak. Desired Result - 2L
    I had been using the below formula to find the current streak but the formula takes zero into consideration and gives me the result as 1L. How can i make the forumla to ignore the zeros in the array?

    =LOOKUP(1E+100,FREQUENCY(IF(A1:R1=LOOKUP("zzz",A1:R1),COLUMN(A1:R1)),IF(A1:R1<>LOOKUP("zzz",A1:R1),COLUMN(A1:R1))))&""&LOOKUP("zzz",A1:R1)

    2) To find the longest winning or losing streak. Desired result: 4w and 3L
    Similarly, I had been using the below formula to find the longest winning and losing streak but the formula takes zero into consideration and give me the result as 3w and 1L.
    How can i make the forumla to ignore the zeros in the array?

    Longest Winning Streak:
    =MAX(FREQUENCY(IF(A1:R1="W",COLUMN(A1:R1)),IF(A1:R1<>"W",COLUMN(A1:R1))))

    Longest Losing Streak:
    =MAX(FREQUENCY(IF(A1:R1="l",COLUMN(A1:R1)),IF(A1:R1<>"l",COLUMN(A1:R1))))

    Thank you so much!

    Best Regards,
    Poko.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    Can you upload example? We a quite lazy for retyping whole post

  3. #3
    Registered User
    Join Date
    10-26-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    Hi Zbor,

    Thank you for your prompt reply!

    Here's the spreadsheet!

    Best Regards,
    Poko
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-26-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    Hi Zbor,

    Any solutions to my problems?

    Thank you so much!

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    Q2 is a relatively easy fix, instead of <>"W" in the second IF you need ="L", i.e.

    =MAX(FREQUENCY(IF(A1:R1="W",COLUMN(A1:R1)),IF(A1:R1="L",COLUMN(A1:R1))))

    Same formula for losing streak except swap "W" and "L"

    A little trickier for Q1 but this should fix it along the same lines....

    =LOOKUP(1E+100,FREQUENCY(IF(A1:R1=LOOKUP("zzz",A1:R1),COLUMN(A1:R1)),IF(A1:R1=IF(LOOKUP("zzz",A1:R1)="W","L","W"),COLUMN(A1:R1))))&""&LOOKUP("zzz",A1:R1)

    ...or this version would be shorter assuming Excel 2007 or later

    =COUNTIF(INDEX(A1:R1,IFERROR(MATCH(2,1/(A1:R1=IF(LOOKUP("zzz",A1:R1)="L","W","L"))),1)):R1,LOOKUP("zzz",A1:R1))&LOOKUP("zzz",A1:R1)
    Last edited by daddylonglegs; 10-29-2011 at 11:17 AM.
    Audere est facere

  6. #6
    Registered User
    Join Date
    10-26-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    Wow! The formulas work perfectly fine!

    Thank you so much!

    Best Regards,
    Poko

  7. #7
    Registered User
    Join Date
    08-24-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    I was wondering how to get the current streak formula to work if the data is entered vertically instead of horizontally. I appreciate the help!

    current streak.xlsx

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Exclude zero when counting streaks (win/loss) and current streaks

    PreLives,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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