Exceljet

Quick, clean, and to the point

Longest winning streak

Excel formula: Longest winning streak
Generic formula 
{=MAX(FREQUENCY(IF(result="w",id),IF(result="w",0,id)))}
Explanation 

To calculate the longest winning streak, you can use an array formula based on the frequency function.

Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter

How this formula works

This is a tricky formula to understand, and it requires that you have a numeric id for each match, and that all matches are sorted by id.

They key is in understanding how FREQUENCY gathers numbers into "bins". Each bin represents an upper limit, and generates a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number.

The gist of this formula is that it creates a new bin at the end of each winning streak using the id of the subsequent loss. All other bins are created as zero. The practical effect is a count of consecutive wins in each bin.

Inside frequency, the data array is generated with this:

IF(result="w",id)

Which creates an array like this:

{1;FALSE;3;4;5;FALSE;7;8;9;10;11;FALSE;FALSE;FALSE}

Notice that only wins make it into this array.

The bins array is generated with:

IF(result="w",0,id)

Which creates an array like this:

{0;2;0;0;0;6;0;0;0;0;0;12;13;14}

Only the losses make it into this array as non-zero values, and they become the functional bins that tally wins. Wins are translated as zeros, and don't actually collect any numbers from the data array, since FALSE values are ignored.

With data array and bin arrays above, frequency returns an array of counts per bin. By wrapping the MAX function around this array result, we get the highest count, which is the longest winning streak.

The MAX function then returns the maximum count.

With a helper column

If you don't like fancy pants formulas that require a full page explanation to understand, you can always use a helper column with a simple formula, and apply MAX to the results. In this case, you can created a running count of wins with this formula in D4, copied down the column:

=IF(C4="w",D3+1,0)
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.