Options #3 and #4 will work in older versions of Excel.
Starting at zero, SCAN runs through the list of results.
When it encounters a “w”, it adds 1 to a running count.
When it encounters any other value, it resets the running count to zero.
SCAN returns the full set of running counts to MAX, which returns the maximum number.
Let’s look at the details.
Otherwise, the accumulator is reset to zero.
Notice the count starts over again at zero when there is a loss.
When there are consecutive wins, it increases.
In other words, we are marking the end of winning streaks.
Otherwise, the count is reset to zero.
if cell C5 contains “W”, because the header text in cell D4 will cause an error.
Do you like tricky formulas?
The key idea is that FREQUENCY gathers numbers into “bins”.
The formula creates a new bin at theendof each winning streak using the row number of the loss.
All other bins are created as zero.
The practical effect is a count of consecutive wins in each bin.
The losses become FALSE.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
More than one condition can be tested by nesting IF functions.
The IF…
LET Function
The Excel LET function lets you define named variables in a formula.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The number of rows and columns to remove is provided by separaterowsandcolumnsarguments.
Rows and columns can be dropped from the start or end of the given array….
