Formulas To Calculate Longest & Current Win Streaks
All About The Wins
As we start to head into the summer months here in the United States, I thought I would shift gears from business analytics to something a little more fun and competitive. In this post we will discuss determining how many consecutive wins a team currently has and also what a team’s longest consecutive win streak has been during the season. This sort of calculation could also be used in a business setting for calculating streaks for profitable months, meeting sales goals, or achieving annual plan targets. If you read on, I will be providing you with an in-depth explanation on how each of the formulas work and provide you with a downloadable example file.
Calculating Streaks
Below you will learn how to calculate both a Current Streak and the Longest Streak. Let’s shorten up the table from above and only look at an Excel table with seven rows (shown below).
How To Calculate The Current Streak
Formula: =COUNTA(Table1[W/L])-MATCH(1,INDEX(1/(Table1[W/L]=”Loss”),0))
The Break Down
- INDEX(1/(Table1[W/L]=”Loss”)
The goal of this portion of the formula is to create an array (list) of only the positions of losses within the table. As you can see in the far right column, there are 2 losses found in the table and they are “flagged” by the numerical value of 1 (binary for TRUE).
- MATCH(1, [index output],0)
The MATCH function then takes the array list that the INDEX function created and searches for the first instance of the value 1 (again binary for TRUE). The MATCH function is unique in that it starts from the end of a given list, essentially working backwards in it’s search. Using this to your advantage, you can find the row number of the last loss the team has incurred. In this example it is game (row) 6.
- COUNTA(Table1[W/L]) – [match output])
Now to the more straight-forward part of this formula. By using COUNTA you can count the number of non-blank cells in the W/L column of the table. This of course if 7 and by subtracting the output from the MATCH formula (6), the entire formula will output a current winning streak of 1.
How To Calculate The Longest Streak
Formula: =MAX(FREQUENCY(IF(Table1[W/L]=”Win”,ROW(Table1[W/L])), IF(Table1[W/L]<>”Win”,ROW(Table1[W/L]))))
NOTE: You need to turn this into an Array Formula. To do this hold down Ctrl + Shift + Enter while clicked inside the formula bar
The Break Down
- IF(Table1[W/L]=”Win”,ROW(Table1[W/L])) and IF(Table1[W/L]<>”Win”,ROW(Table1[W/L]))
These IF formulas are array formulas. This means that they will output a list of answers instead of a single numerical value. In the table to the left you can see there is a column for each IF statement formula’s outputs. The first IF formula outputs the row number of any win. If the cell in the W/L column does not not equal “Win”, the IF formula outputs a FALSE (or 0 in binary). The second IF formula does just the opposite. It only outputs row numbers for cells that do not equal (< >) “Win”. Likewise, a zero value is the output for any “Win” valued cell.
- FREQUENCY( [if statement 1] , [if statement 2] )
Here’s the really tricky part of the formula. This is in part because most people don’t use the FREQUENCY function too often and it can be a little hard to get a grasp on. The FREQUENCY function outputs an array and has 2 inputs. It’s first input requires a list of numbers, the second input requires another list of numbers. Basically what the FREQUENCY function does is it goes down the second input’s list of numbers and determines how many numbers in the first input list are less than or equal to that number. Once those numbers are chosen, they are ineligible from being used in any of the other numbers in the second input.
Are you confused yet?! Let’s walk through the examples on the left.
In the first picture the function is looking for any values in IF #1 list that are < or = 0. It finds two values that match the criteria and respectively outputs a 2. Those two values that matched the criteria are now ineligible to be considered again.
In the second picture the function is now looking for any values that are < or = to 2. Out of the remaining numbers in the IF #1 list, only one number fits the criteria. This means that the value 1 is the second output for the FREQUENCY function.
Finally, in the third example picture, the function is (again) looking for any values that are < or = 0. Of the remaining values in IF #1, there are no numbers that fit the criteria. The FREQUENCY function outputs a 0 as it’s third value.
Hopefully, you were able to get a grasp on how to use the Frequency function. For more information, you can visit Microsoft’s help page How to Use the FREQUENCY Function. Now let’s finish the formula explanation by covering the final piece.
- MAX( [frequency output] )
The MAX function is fairly straight forward in that it returns the highest number out of a list of values. The function will cycle through the array list provided from the FREQUENCY function and figure out which number is the highest. This effectively calculates the longest streak of wins!
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![[Solved] Excel Found A Problem With Formula References In This Worksheet](https://www.thespreadsheetguru.com/wp-content/uploads/2023/08/Excel-Found-Problem-With-Formula-References.svg)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.