Count repetitions of occurences of value

Hi all,

I've got quite a challenge for the smart guys amongst us.

count repetitions.jpg

Attached pic shows a simplified example of what I am trying to do.

I've got a large number of rows, in which a's and b's are repeated randomly. Now I am looking for a formula resulting in the max number of consecutive a's (meaning repetitions), without interruption by b's and the same for the b's.

I also want to find the average repetitions of each value.

An extra challenge is that the last group of values should be ignored, because incomplete (the columns are dates and still counting).

Give it your best shot and thx in advance!

Gert

Thx FDibbins, will do!

Here is the table (remember, it's a very simple example, my sheet is quite big)

A	B	C	D	E	F	G	H	I
a	a	a	b	a	a	b	b	b
a	a	a	a	b	b	a	a	a
a	b	b	b	b	b	b	b	a

Expected answers:

J	K	L	M
Max repetitions a	Max repetitions b	Average repetitions a	Average repetitions b
3	1	2.5	1
4	2	4	2
1	7	1	7