Frequency question

[marc01]

Hi all,

Apologies in advance for the excessive post, but hope it makes sense.​

I am in the process of analysing a tennis match. I would like to look at how many times a player wins just 1 point at a time, 2 points in a row, 3 in a row 4 in a row and 5+ points in a row.

From my raw data is have a column titled 'Point won by' and then the players name is indicated. e.g.

Point won by
Player A
Player A
Player B
Player A
Player B
Player A
Player A
Player A
Player B
Player A
Player A


So, player A has won 1 point in a row once, player B has done this 3 times,
player A has won 2 points in a row 2 times, player B has done 0
player A has won 3 points in a row 1 time, player B has done 0
and so on up to tracking 5+ points.

Unsure if this would make it easier but I have used the formula =IF(F2=$Q$1,1,0) to basically show a 1 when the player won the point and 0 when they lost. I have this in a column for each player.

I also have used the following formula =IF(Q2,SUM(R1)+1,0) to get a running total of points won (1 is shown), that resets when a point is lost (0 is shown).

Issue with the above is that for example I have the following:
1
0
1
0
0
1
2
3
4
0

Don't require the 1,2,3 in the successive points won, just that they managed to win 4 points in a row.

Does anyone have any idea how I can extract this data? Have played around with frequency function but to be honest not that experienced using that just now.

Any assistance would be much appreciated.



Best,

Marc

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J
1	Point won by			Points in a row:	1	2	3	4	5+
2	Player A			Player A	1	2	1	0	0
3	Player A			Player B	3	0	0	0	0
4	Player B
5	Player A
6	Player B
7	Player A
8	Player A
9	Player A
10	Player B
11	Player A
12	Player A

<tbody>
</tbody>

Sheet23​

Array Formulas Cell Formula E2 {=SUM(IF(FREQUENCY(IF($A$2:$A$12= $D2,ROW($A$2:$A$12)),IF($A$2:$A$12<>$D2,ROW($A$2:$A$12)))=E$1,1))} I2 {=SUM(IF(FREQUENCY(IF($A$2:$A$12= $D2,ROW($A$2:$A$12)),IF($A$2:$A$12<>$D2,ROW($A$2:$A$12)))>4,1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the E2 formula in, change the ranges to match your sheet, and confirm with Control+Shift+Enter. Then you can copy it to the rest of the cells in E2:H3. Then put in the I2 formula the same way, and copy to I3.

That should do it for you. Let us know.

 

[marc01]

Fantastic Eric, many thanks for you help!