This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Find the Longest Win Streak
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.
Figure 315 Find the longest winning streak.
Strategy: Add a column that will calculate the current winning streak. Then, look for the MAX of that column.
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 add the number 1 to a heading of “Win Streak" in cell E1, you will get a #VALUE error.
The figure above shows one solution: adding a blank row 2 and setting the row height to be very small. A second solution is to have a different formula in E2 than all of the other cells. Both of these solutions make it tough to sort the data (perhaps you want to find the longest Home winning streak so you want to sort by column C.)
A third solution exists. Put the number 0 in the heading for E1. Then, use Ctrl+1 (Ctrl+One) to get to the Format Cells dialog. On the Number tab, choose Custom in the Category list. Type a custom number format of “Winning Streak" including the quotes. This custom number format will allow a zero to be stored in E1, but will force Excel to show the words “Winning Streak" instead.
Figure 316 Store a zero, but display a heading.
The formula for E2 needs to see if D2 contains a W or an L. If there is a loss, then the winning streak starts over at zero. If there is a win, then add 1 game to the previous winning streak. When you have a conditional calculation like this, use the IF function. The IF function allows one of two calculations depending on the result of a logical test. In this case, the logical test is D2="W". If that is true, the formula should be 1+E1. Otherwise, the formula should be 0. The formula is =IF(D2="W",1+E1,0).
When you copy that formula down to all rows, it will calculate a winning streak.
Figure 317 Calculate a winning streak.
To find the longest winning streak of the season, use =MAX(E:E).
Figure 318 Find the longest winning streak.
Additional Details: To find the win/loss record, you can use a formula of =COUNTIF(D:D,"W")&"-"&COUNTIF(D:D,"L").