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.
