What is the length of longest winning streak? [Excel homework]

Posted on January 30th, 2015 in Excel Challenges - 22 comments

Here is a fun problem to think about.

Let’s say you are looking at some data like this:

What is the length of longest winning streak - Excel formula problem

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Download the workbook and solve this problem.

Note: this scenario is useful for many situations like finding longest attendance streak in employees, longest occupancy streak in hotel rooms, longest fault free production etc.

Few things to keep in mind:

  • Assume the list of wins contains only Boolean values and named as list
  • Assume the match number column is named as id
  • You can use these additional names too:
    • list.a (represents one cell above the list and all list values except the last one)
    • list.b (all list values except the first one and extra blank cell at the end)
    • size (the size of list, counta(list))
  • You can use single formulas, helper columns or VBA to solve this problem
  • Post your solutions in the comments.
  • If your solution uses < > symbols, write LT & GT instead. Otherwise few bits of your comment might be gobbled by byte hungry monster that handles our comment program in server.

Go ahead and figure out the solution. Happy hunting.

Hungry for more? Check out Excel homework problems page.

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “What is the length of longest winning streak? [Excel homework]”

  1. Green says:

    Using VBA to CountIf Until and loop until the cells are blank at the bottom. That would give a list of the sizes of the groups of trues. Then a MAX formula to calculate the largest figure in the returned figures.

  2. =MAX(FREQUENCY(IF(list,id),IF(list,,id)))
    regards
    r

  3. assuming that in F6 there is the formula that solves the first question ... so the formula for bonus is:
    =MATCH(F6,FREQUENCY(IF(list,id),IF(list,,id)),)-F6

    • Louis Bharnabas says:

      Hi Roberto,

      Can you explain how both the formulas work?

      Regards,
      Louis

    • Cody says:

      This is a cool, simpler alternative to Chandoo's solution. The only issue is that this method only works when dealing with True/False values. if the values were switched to numbers or text it would return an error.

  4. Dick Byrne says:

    Adding a helper column is definitely the simplest and quickest way to go here. In cell D5, put =IF(NOT(C5), 0, 1+D4) and copy that down the list. Then the longest streak is =MAX(D5:D244) and it starts at =MATCH(I6,D5:D244,0) - I6 + 1

    • Dick Byrne says:

      That "Starts At" formula assumes that you've put the longest streak formula in cell I6.

    • SantoshKumar says:

      Thanks Byrne,
      Simple If & Not fromula through helper column is great.
      Also changing the value position like, =IF(NOT(C5),1+D4,0) gives longest loosing streak.

  5. Chris Macro says:

    If anybody needs a little help tackling this homework assignment, below is a link to an article I wrote a while back that shows how to calculate streaks

    http://www.thespreadsheetguru.com/blog/2014/6/29/formulas-to-calculate-longest-current-win-streaks

  6. Stef@n says:

    amazing 🙂
    chandoo's solution shows the longest winner or loser-track
    robert mensa's solution shows only the winner-track
    Chris macro's solution is a wonderful supplement
    THANK YOU to all

    will be looking for more solutions at this weekend
    thank you chandoo for this challenge;)

    Regards
    Stef@n

  7. KathyW says:

    I just put a formula in column A, starting in cell A5 (which I then hid):
    =IF(C5=TRUE,1+A4,0)

    Then in cell F6 I put : =MAX(A5:A245)
    In cell F7 I put: =VLOOKUP(F6,A5:C245,2,FALSE)-(F6-1)

    This only tracks winning streaks.

  8. Mark says:

    Using helper columns
    in cell d5: =IF(C5=C4,1+D4,1)
    Copy all the way down.
    Start is: =INDEX(B5:B245,MATCH(MAX(D5:D245),D5:D245,0))-MAX(D5:D245)+1

    Explained:
    Length: =MAX(D5:D245)
    Location of that Max: =INDEX(B5:B245,MATCH(E5,D5:D245,0)) Returns 98.
    Therefore Started when is the location (98) minus the (Max+1)

  9. Mehmet Gunal OLCER says:

    By using the helper column J

    J5=IF(C5;SUM(J4;1);0)

    Length: F10=MAX(J:J)

    Started When: F11=MATCH(F10;J:J;0)-F10-3

  10. Jason Morin says:

    Helper column in C. So in C2:

    =B2*(B1=0)+B2*B1*(C1+1)

    Now take the max of column C:

    =AGGREGATE(4,6,C:C)

    To make the results in column C look more legible to the user, you could also use this in C2:

    =IF(AND(B2,B1=FALSE),1,IF(AND(B2,B1),C1+1,""))

  11. Ghazanfar J says:

    @roberto mensa

    What sorcery is this ?!?!

  12. Chandoo says:

    Excellent solutions everyone. Special thanks to @Roberto for sharing a very elegant and simple solution. Thanks to Chris for linking to a valuable resource.

    Here is what I came up with. Both array formulas.

    Streak length (in cell F6)

    =MAX(IFERROR(SMALL(IF(list.b LT GT list,id),id)- SMALL(IF(list.aLT GT list,id),id), 0))+1

    Streak position:

    =SMALL(IF(list.a LT GT list,id),MATCH(F6-1,IFERROR(SMALL(IF(list.b LT GT list,id),id)- SMALL(IF(list.a LT GT list,id),id), 0),0))

    Notes:

    1. list.a (represents one cell above the list and all list values except the last one)
    2. list.b (all list values except the first one and extra blank cell at the end)

  13. Navin says:

    Created a column to start counting the streak (from cell D5) :
    =IF(C5=TRUE,D4+1,0)
    To get the length of longest winning streak :
    =MAX(D5:D244)
    To get the ID where it started :
    =INDEX($B$5:$D$244,MATCH(G6,$D$5:$D$244,0)-G6+1,1)

  14. Steve S. says:

    I used VBA to come up with this solution:

    Sub Winningtreak()

    Dim CurrentStreak As Long
    Dim LongestStreak As Long
    Dim FinalRow, x, y As Long

    FinalRow = Cells(Rows.Count, 3).End(xlUp).Row + 1
    CurrentStreak = 0
    LongestStreak = 0

    For x = 5 To FinalRow

    If Cells(x, 3) = "True" Then

    CurrentStreak = CurrentStreak + 1

    ElseIf CurrentStreak GT= LongestStreak Then
    LongestStreak = CurrentStreak
    CurrentStreak = 0
    y = x - LongestStreak - 4
    ElseIf CurrentStreak LT LongestStreak Then
    CurrentStreak = 0

    End If

    Next x

    Cells(4, 6) = LongestStreak
    Cells(4, 7) = "Starting at match " & y

    End Sub

  15. Chris says:

    Hi all,
    This my first time on this site. Wow! I specialize in dynamic charts which I use to teach math and I came here to look for "hover" possibilities but I was captivated by this homework. What a great site!!!
    I don't know how this column works but replaced all items in the won column with letter grades: A,B, C, D, F. It is easy to count the most winners but what do I do to count the number of each grade letter? Any tutors to help me please?

Leave a Reply