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
- 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.
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.
Leave a Reply
|« Doing Cost Benefit Analysis in Excel – a case study
|2 Must watch Excel webinars for you »