To calculate the longest winning streak in Excel, you can use a formula based on the SCAN function with the LAMBDA and MAX functions.
How to calculate the longest winning streak in Excel
Steps to calculate the consecutive wins:
- Select cell F3.
- Type =MAX(SCAN(0,D3:D22,LAMBDA(a,v,IF(v=”W”,a+1,0))))
- Press Enter.
- The formula returns the number of longest consecutive wins in a range.
Example
The goal is to create a formula that calculates the longest consecutive winning streak in the range D3:D22, where “W” represents a win and “L” represents a loss.
Formula:
=MAX(SCAN(0, D3:D22, LAMBDA(a,v,IF(v=”W”,a+1,0))))
The formula returns the number of most consecutive wins.
Explanation
Evaluate the formula from the inside out.
SCAN(0, D3, LAMBDA(a, v, IF(v=”W”, a+1, 0))): The SCAN function applies a calculation across each element in the specified range D3:D22, accumulating a result along the way. This part of the formula returns an array where each element represents the cumulative result at each position. The initial value is 0, which is the initial value for the accumulator. This means the calculation starts with a value of 0 before iterating through the range.
LAMBDA(a, v, IF(v=”W”, a+1, 0)): “a”: represents the accumulated result so far. “v”: represents the current value in the range D3:D22 (each cell in the range). IF(v=”W”, a+1, 0): This checks if the current value (v) is “W”: If v is “W”, it increments the accumulated value by 1 (continuing the winning streak). If v is not “W” (i.e., it’s “L”), it resets the accumulator to 0, ending the current winning streak. As a result, SCAN produces an array representing the length of the consecutive wins up to each point in D3:D22.
MAX(…): MAX takes the resulting array from SCAN and finds the highest value, which corresponds to the longest winning streak. This is because each winning streak accumulates a series of increasing values (e.g., 1, 2, 3…) for consecutive “W”s, and the streak resets to 0 whenever an “L” is encountered.
D3:D22 contains a series like {W, W, W, L, L, W, W, W, W, W, W, L, L}, the SCAN function would generate an array like {1, 2, 3, 0, 0, 1, 2, 3, 4, 5, 6, 0, 0}. Then, MAX finds the highest value in this array, 6, representing the longest streak of six consecutive wins.
Formula to list all winning streaks
If you want to list all winning streaks in the given period, use the formula below:
=LET(
streaks, SCAN(0, D3:D22, LAMBDA(currentStreak, result, IF(result = "W, currentStreak + 1, 0))),
shifted, VSTACK(DROP(streaks, 1), 0),
ends, (streaks > 0) * (shifted = 0),
FILTER(streaks, ends)
)
Explanation:
Streaks Calculation: SCAN iterates over each cell in the range D3:D22, producing an array by applying the function defined by LAMBDA. 0 is the initial value, representing the starting count for the first element in the array. LAMBDA(currentStreak, result, IF(result = “W”, currentStreak + 1, 0)) is applied to each element in D3:D22:
- currentStreak represents the ongoing streak count (initially 0).
- the result represents each individual value in D3:D22.
- IF(result = “W”, currentStreak + 1, 0) checks if the current value (result) is “W”: If the result is “W”, it increments currentStreak by 1. If the result is not “W”, it resets currentStreak to 0.
This part generates an array (streaks) that tracks consecutive counts of “W” in D3:D22.
Shifted Calculation: DROP(streaks, 1) removes the first element of the streaks array, effectively shifting it down by one position. VSTACK(…, 0) adds 0 at the end of the shifted array, making it the same length as streaks. The result shifted, representing the previous values in streaks with a 0 at the end.
Ends Calculation: Ends defined as (streaks > 0) * (shifted = 0) and creates a logical array where (streaks > 0) identifies positions in streaks with a non-zero streak count and (shifted = 0) identifies where the previous cell in streaks was zero, indicating the end of a winning streak. Multiplying these conditions with (streaks > 0) * (shifted = 0) results in an array where 1 (TRUE) marks the end of each streak.
Finally, FILTER extracts only the elements in streaks marked as the ends of winning streaks, thus returning the length of each completed streak.
Workaround with FREQUENCY function
If your Excel version does not have dynamic array support, you can use the following formula to calculate the longest consecutive winning streak (longest sequence of “W”) in the range D3:D22:
=MAX(FREQUENCY(IF(D3:D22=”w”,ROW(D3:D22)),IF(D3:D22=”w”,0,ROW(D3:D22))))
Let’s evaluate the formula:
=IF(D3=”W”, ROW(D3), 0):
This formula creates an array that includes the row numbers of cells in D3:D22 if they contain “W”. If a cell does not contain “W”, it returns 0. For example, if “W” values in rows 3, 4, and 5, this part will output {3, 4, 5, 0, 0, …}. The FREQUENCY function will use this array as the data_array argument.
=IF(D3=”W”, 0, ROW(D3)):
This part of the expression creates an array with the row numbers where cells in D3:D22 do not contain “W” (i.e., they contain “L”). If a cell does contain “W”, it returns 0. The array represents the bins_array argument for FREQUENCY and effectively separates streaks of “W” by treating non-“W” rows as breakpoints.
FREQUENCY Function: FREQUENCY(data_array, bins_array) counts how many consecutive numbers appear between each non-zero value in bins_array. By using ROW values in the data_array for “W” cells and bins_array for “L” cells (or zero values), FREQUENCY calculates the lengths of consecutive streaks of “W” in D3:D22. The result is an array where each value represents the length of a streak of “W” values.
Finally, MAX finds the maximum value in the array returned by FREQUENCY, representing the longest winning streak (the maximum consecutive “W” streak) in D3:D22.