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.
- 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.
- 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.
- Calculate a winning streak.
To find the longest winning streak of the season, use =MAX(E:E).
- 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").
- Count the number of W and L values.