Find the Longest Win Streak

January 31, 2022 - by Bill Jelen

Problem: I have some baseball data with a column showing W or L for wins and losses. I want to calculate the longest winning streak.

Strategy: Add a helper column to calculate the current winning streak using =IF(C2=”W”,SUM(1,D1),0). Then, look for the MAX of that column.

Column C contains 162 game results, either W for Win or L for Loss. You want to calculate the longest winning streak. — Figure 306. The formula in D2 is =IF(C2=”W”,SUM(1,D1),0).

This formula is a classic type of formula that looks at a cell in the current row, makes a decision, and then adds to the value calculated in the previous row. This works great in all cases except in row 2. If you try to calculate =D1+1, you will get an error. Instead, use SUM(D1,1) as the SUM function will ignore the text in D1.

Additional Details: To find the win/loss record, you can use a formula of =COUNTIF(D:D,”W”)&”-”&COUNTIF(D:D,”L”).

Show a Win-Loss using =COUNTIF(D:D,"W")&"-"&COUNTIF(D:D,"L"). — Figure 307. Count the number of W and L values.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tim Mossholder on Unsplash