Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter 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: 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: 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:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
