Maximum Consecutive wins OR How maximum times, an Event has occurred Consecutively (Using Frequency Function)

Hi Everyone,

This time, I have come up an excel function which will give you maximum consecutive Wins count for different teams. To be more precise, this article is gonna be about finding maximum consecutively occurring event OR we can say that how maximum times, an event has consecutively occurred. The Function I have used, uses Frequency function in its core. To start with explaining the Win part, I have used following Formula to calculated consecutive wins count for team Say India:-

=MAX(FREQUENCY(IF(($A$1:$A$32=$E$1)*($B$1:$B$32=$D2),ROW($B$1:$B$32)),IF(($A$1:$A$32=$E$1)*($B$1:$B$32<>$D2), ROW($B$1:$B$32))))

I will start by explaining the Frequency function. I have used
IF(($A$1:$A$32=$E$1)*($B$1:$B$32=$D2),ROW($B$1:$B$32)) as Data Array Parameter
AND
IF(($A$1:$A$32=$E$1)*($B$1:$B$32<>$D2),ROW($B$1:$B$32)) as Bin Array Parameters

If I evaluate both parameters simultaneously, It will result in following table.

		Data Array		Bin Array
		FALSE		FALSE
		FALSE		2
		3		FALSE
		4		FALSE
----------------------------->		5		FALSE	<-------------------------
Row numbers of India's wins		FALSE		6	Row Number of India's Loss
otherwise False.		7		FALSE	otherwise False.
		8		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE
		FALSE		FALSE

Now before I explain the above two arrays, there are somethings which you should know about Frequency Function.

1. The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the no. of values above the highest interval. 

2. FREQUENCY ignores blank cells and text in Data Array and Bin Array both.

3. FREQUENCY Function ignores Duplicates in the Bin_Array and treats only first occurrence.

4. FREQUENCY Function returns an array so must be entered as an array formula.

Better if you read the following article which explains about frequency function.

http://excel-buzz.blogspot.in/2015/01/frequency-function-explained.html

I guess, till now, you should be knowing that Frequency function counts the values in Data array with regard to the Groups OR Bins gets available from Bin Array. The values of Data and Bin array are treated having regard to above conditions. Here is how the above arrays are further processed.

Bins	Count of Data Array values fall in Bins
0-2	0	-
2-6	3	(3,4,5)
6-?	2	(7,8)

So the above count conveys that India has won 3 times and 2 times consecutively. So the Maximum would be 3. Using Max Function, we can return the Maximum one that is 3.

So this was an other quality of Frequency function, which I explained with the above example. If you have any query or suggestion, then do comment.

Check the attached example file:-

Max Consecutive wins.xlsx

Regards,

Vikas Gautam