Calculate win streak

[jbifeelgood]

Hi,

I have table tennis match data for 3 people;
Person (1) D3:R3
Person (2) D4:R4
Person (3) D5:R5

Where the values are 2 (win) 1 (draw) and 0 (loss). I would like to calculate the longest win and loss streak for each.

This is also then repeated in D9:R9, D15:R19, D20:R20 (person a), D10:R10, D16:R16, D21:R21 (person b), D11:R11, D17:R17. D22:R2, not sure if it would be possible to include all of these ranges for each person as well?

 

[Eric W]

Welcome to the Board!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Longest win streak	Longest loss streak
2	2	1
3	3	2	Andy	2	1	2	2	2	0	1	1	2	1	2	1	1	2	0
4	6	1	Serena	2	2	2	2	2	2	0	1	2	2	2	1	2	2	1
5	2	2	Roger	1	1	2	1	0	1	2	2	1	2	2	1	2	0	0
Sheet4

Cell Formulas
Range	Formula
A3:B5	A3	{=MAX(FREQUENCY(IF($D3:$R3=A$2,COLUMN($D3:$R3)),IF($D3:$R3<>A$2,COLUMN($D3:$R3))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I'm not sure what you mean when you say the ranges are repeated later. Do you mean that the range for person A should be D3:R3 and then continues in D9:R9, so that a 2 in R3 and a 2 in D9 is a streak of 2?

 

[jbifeelgood]

Welcome to the Board!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Longest win streak	Longest loss streak
2	2	1
3	3	2	Andy	2	1	2	2	2	0	1	1	2	1	2	1	1	2	0
4	6	1	Serena	2	2	2	2	2	2	0	1	2	2	2	1	2	2	1
5	2	2	Roger	1	1	2	1	0	1	2	2	1	2	2	1	2	0	0
Sheet4

Cell Formulas
Range	Formula
A3:B5	A3	{=MAX(FREQUENCY(IF($D3:$R3=A$2,COLUMN($D3:$R3)),IF($D3:$R3<>A$2,COLUMN($D3:$R3))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I'm not sure what you mean when you say the ranges are repeated later. Do you mean that the range for person A should be D3:R3 and then continues in D9:R9, so that a 2 in R3 and a 2 in D9 is a streak of 2?

That's amazing, thank you! The ranges repeated later are a bit more complex... what you've given will do the job nicely. Thank you for your time and help!

 

[Eric W]

Happy to help!