Here is a fun problem to think about.
Let’s say you are looking at some data like this:
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.
22 Responses
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.
=MAX(FREQUENCY(IF(list,id),IF(list,,id)))
regards
r
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
Hi Roberto,
Can you explain how both the formulas work?
Regards,
Louis
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.
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
That “Starts At” formula assumes that you’ve put the longest streak formula in cell I6.
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.
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
Interesting post and comments, I also wrote a blog post about something similar a few months ago.
http://www.get-digital-help.com/2014/11/11/find-the-longestsmallest-consecutive-sequence-of-a-value/
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
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.
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)
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
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,””))
@roberto mensa
What sorcery is this ?!?!
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)
would you please tell me how you set list, id, list.a, list.b,
Thanks in advance
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)
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
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?