# Formulas To Calculate Longest & Current Win Streaks

**Updated:**06/29/14 • 6 min read

**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,
,0)*[index output]*

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:** Y*ou 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

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