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

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?

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
• 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. Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: advanced excel, homework, Learn Excel, Microsoft Excel Formulas 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. roberto mensa says:

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

3. roberto mensa says:

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

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)

• Ashfaqur Rahman says:

would you please tell me how you set list, id, list.a, list.b,

13. […] What is the length of longest winning streak?  […]

14. 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)

15. 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

16. 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?

 « Doing Cost Benefit Analysis in Excel – a case study 2 Must watch Excel webinars for you »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.