...

Find how many times two specified members will be contained in a series of sets

 

Below is an Excel table recording the groups in a competition. In the range of C1:V13, every 6 columns corresponds to a table having 4 players, and 1-16 represents numbers of 16 players.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

1

8

14

11

12

2

1

4

3

13

10

15

16

6

5

2

2

3

1

5

7

9

11

13

15

2

4

6

8

10

12

3

3

11

8

14

15

3

6

9

12

13

16

5

2

1

4

4

4

9

13

1

5

4

8

12

16

3

7

10

14

2

6

5

5

3

2

11

16

14

7

10

15

1

6

12

13

8

5

6

6

2

14

3

9

7

1

13

8

5

11

6

4

15

12

7

7

12

6

13

7

4

5

12

11

1

8

15

9

2

16

8

8

1

9

2

10

7

15

8

16

3

11

4

12

5

13

Task: Compute the frequency of any two players who compete on the same table and display the result as a matrix diagram, as the following shows:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

11

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

12

1















13

2

2














14

3

2

3













15

4

2

2

2












16

5

2

1

1

2











17

6

1

2

1

2

2










18

7

2

0

2

0

1

1









19

8

2

1

0

2

1

1

2








20

9

3

2

2

0

1

1

0

1







21

10

1

1

1

0

0

0

2

0

1






22

11

0

1

2

3

2

1

0

2

1

0





23

12

1

0

2

3

1

3

1

2

1

1

3




24

13

3

1

0

0

3

2

2

1

2

1

1

2



25

14

0

1

2

0

0

0

2

2

1

2

2

1

0


26

15

1

0

0

0

0

0

2

3

2

2

2

1

2

2

27

16

0

3

1

1

1

0

1

2

0

1

1

1

2

0

2

Enter the following formula in C13 in SPL XLL:

=spl("=?1.conj(~.group((#-1)\6)).count( ~.contain( ?2,?3) )",$C$1:$V$8,$B13,C$11)

The formula gets result only for one cell, and we need to drag it to the other cells to compute their values while avoiding cell on the diagonal (because it is meaningless to put one player on the same table twice). Note that the matrix will compute twice, so you just need to drag to draw a rectangular area.

Picture1png

group()function groups rows; # is the sequence number of the current member, and ~ is the current member. contain() function finds if the specidied items all members of a certain sequence.

Source:https://www.reddit.com/r/excel/comments/1cceq2q/how_to_count_how_many_times_two_players_are_at/


 
 •  81 View  •