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
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:
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:
The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of each value in a range. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter.
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
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.