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.
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
The Break Down
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 off 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 The Example File
If you are still unclear of how everything works or just want to save some time and build off an example, go ahead and click the download link below to see how I setup my winning streak calculations and dashboard. If you have any questions or other ideas relating to streak calculations, submit your comments in the commenting section below! I look forward to hearing from you!
As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
Share This Post!
Did you find this post helpful? Do you want to support this blog because you're just that awesome?! By sharing this post on Facebook, Twitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website. Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation. Learning is the whole reason why this blog exists! If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium. Thank you so much for reading and I hope I can continue to provide you with great content in the future! Cheers!