Excel: Dynamic Arrays for Longest & Current Winning Streak

In this post, we’ll explore how to use the SCAN and FREQUENCY functions in Excel to calculate both the longest and current winning streaks.

Tracking performance trends over time, such as a team’s winning streak, can provide valuable insights in various fields, including:

Business performance tracking
Market trend analysis
Employee performance evaluations
Customer behavior analysis
Quality control in manufacturing
Health and fitness monitoring
Academic performance tracking

With Excel’s dynamic array formulas, you can easily calculate these streaks in a series of results represented by wins (denoted as “W”) and losses (denoted as “L”).

The Power of Dynamic Arrays in Excel

Using Excel’s dynamic array functions like SCAN and FREQUENCY, you can efficiently analyze a sequence of results without manually counting or sorting data. These functions allow you to dynamically update your results whenever new data is added, making the process seamless and adaptable.

We will walk through two methods:

SCAN function: Provides a flexible approach by accumulating values with a custom LAMBDA function.
FREQUENCY function: Excellent for calculating all streaks, including the current one.

When you have a large volume of data in your Excel spreadsheet, I recommend using the FREQUENCY function, as LAMBDA functions are known to slow down or break with large datasets.

Let’s dive into the formulas and see how they work with a sample dataset.

Sample Data in Excel

Assume you have a series of games where wins are represented by “W” and losses by “L”. The data range is in cells D3:D13.

Sample data of wins (W) and losses (L) for winning streak analysis in Excel

Calculating the Longest Winning Streak in Excel Using SCAN

To calculate the longest winning streak using the SCAN function in Excel, enter the following formula in cell F3:

=MAX(
   SCAN(0, D3:D13, LAMBDA(a, v, IF(v="W", a+1, 0)))
)

Explanation of the SCAN Formula

The SCAN function scans each value in the array and applies a LAMBDA function, accumulating intermediate results:

Syntax:

SCAN(initial_value, array, function)

LAMBDA(a, v, IF(v="W", a+1, 0)): This lambda function checks if the current element (v) is equal to “W”. If true, it adds 1 to the accumulator (a); otherwise, it returns 0.

The result of this formula will be an array like this:

{1; 0; 1; 2; 0; 1; 2; 3; 0; 1; 2}

The MAX function then finds the highest value in this array, which represents the longest winning streak (in this case, 3).

Calculating the Longest Winning Streak Using FREQUENCY in Excel

You can also calculate the longest winning streak using the FREQUENCY function. Enter this formula in cell F3:

=MAX(
   FREQUENCY(
      IF(D3:D13="W", ROW(D3:D13)), 
      IF(D3:D13<>"W", ROW(D3:D13))
   )
)

I prefer this method over the SCAN function due to its simplicity and efficiency.

Formula Breakdown

Syntax:

FREQUENCY(data_array, bins_array)

IF(D3:D13="W", ROW(D3:D13)): This part defines the data_array, which returns the row numbers of the winning rows and FALSE for the other rows:

{3; FALSE; 5; 6; FALSE; 8; 9; 10; FALSE; 12; 13}

IF(D3:D13<>"W", ROW(D3:D13)): This part defines the bins_array, returning the row numbers of the losing rows and FALSE for the other rows:

{FALSE; 4; FALSE; FALSE; 7; FALSE; FALSE; FALSE; 11; FALSE; FALSE}

The FREQUENCY function then returns the distribution of winning streaks. The MAX returns the final result.

Getting All Winning Streaks in Excel

To retrieve all winning streaks, simply remove the MAX function from the formula:

=FREQUENCY(
   IF(D3:D13="W", ROW(D3:D13)), 
   IF(D3:D13<>"W", ROW(D3:D13))
)

Longest and All Winning Streaks in Excel

Finding the Current Winning Streak in Excel

To find the current winning streak, use the CHOOSEROWS function combined with FREQUENCY:

=CHOOSEROWS(
   FREQUENCY(
      IF(D3:D13="W", ROW(D3:D13)), 
      IF(D3:D13<>"W", ROW(D3:D13))
   ), -1
)

This formula extracts the last value from the array of winning streaks, giving you the current winning streak.

Conclusion

Dynamic array functions like SCAN and FREQUENCY in Excel make it easy to calculate winning streaks without relying on complex helper columns or manual counting.

Whether you want to track the longest winning streak, list all streaks, or identify the current winning streak, these formulas provide a flexible and efficient solution.

These tools enable you to dynamically update your analysis as new data is added, enhancing the robustness and insights of your Excel spreadsheets.

Additionally, these formulas are versatile and can be adapted to various use cases beyond tracking wins and losses. Whether you’re analyzing business performance, monitoring task completion over time, or tracking personal habits, Excel’s dynamic arrays offer the power and flexibility you need.

Try out these formulas in your Excel spreadsheets and take your data analysis to the next level!