Scoring table

[Rock5150]

Hello. I have posted here before if you see my profile for golf scores. I would like to see if someone can assist in creating a table where a designated "1" is given if the person wins a match,, "0" for a tie, and "-1" for a loss. In short, a match would be between two people where each hole, the person with the lower score would win, if both tie, then it's a tie for that hole. Obviously if one person wins, the other person loses the hole. Since each match is 18 holes, between two people, then the accumulation would be the winner would be the person with more hole wins than the other. So for example, Person A wins holes 1,5,6,7,11,13,14, 18. Person B wins outright holes 2,3,4,8,15,16. The ties would be 9,10,12,17. So in the table, Person A has 8 wins, B has 6 wins and 4 ties. Therefore in. my summary table, Person A would get a "1, and since the ties do not even out, then Person B would get "-1". That is in its simplest form. The table I would like is if I have each person's scores in a row format, this created table can pull from the scoring data. My goal is Person A would compete against B, C, D, E (simple format). Can someone help me create a formula?

So here is the raw data with the actual scores. It's Column A:S. Column A is the name. There are five rows. Assume no headers here which is fine

Now the kicker is I want to create a table so that its calculated that A plays B, plays C, D, etc. Obviously I don't need a score of A vs. A

So an example would be below. A formula to calculate the -1,1,0 and then a simple sum to total the "1s", "-1" and "0" per person vertically added. The numbers below I made up but shown for visual. I would have the raw data in one sheet and the below table in another sheet but all in the same workbook.

I

 

[shaowu459]

Hi, please post sample data and expected result using XL2BB.

 

[Rock5150]

To clarify, in a match, if Person A has 5 wins and Person B has 5 wins (holes won), and the other 8 are ties, the A and B in the second table each Get a "0" in that case.

 

[Rock5150]

I use a Mac with Microsoft Excel. Will this be an issue? I am not experienced with the xl2BB

 

[Rock5150]

Can someone assist here? My computer is not allowing this add-in and blocking. I tried to go to options add-ins and allow macros but my work computer is not allowing. I have previously not used the add-in before. Thanks

 

[Rock5150]

Ok I was able to get the add in to work . Here are the two items as noted above

HD Ham and Egg spreadsheet for HD Invitational May 2024 everyone has everyone.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Person A	3	5	3	3	3	2	6	6	2	3	5	4	2	3	4	3	5	7
2	Person B	4	5	4	2	3	3	4	5	3	3	5	4	3	2	4	4	5	4
3	Person C	4	4	3	4	5	3	4	5	3	3	5	4	2	3	4	4	4	4
4	Person D	4	4	7	5	4	4	6	5	5	4	6	5	3	3	4	4	5	5
5	Person E	4	5	3	3	4	3	4	5	3	3	5	6	3	2	4	3	5	4
Sheet6

HD Ham and Egg spreadsheet for HD Invitational May 2024 everyone has everyone.xlsx
	A	B	C	D	E	F
1		Person A	Person B	Person C	Person D	Person E
2	Person A	-1
3	Person B	1
4	Person C	1
5	Person D	1
6	Person E	0
7
8	Total wins (1)	xx	xx	xx	xx	xx
9	Total loses (-1)	xx	xx	xx	xx	xx
10	Total ties (0)	xx	xx	xx	xx	xx
Sheet7

 

[Rock5150]

Hi, please post sample data and expected result using XL2BB.

Ok I updated the post with the xlbb layout in my message. Thanks

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Person A	3	5	3	3	3	2	6	6	2	3	5	4	2	3	4	3	5	7			Person A	Person B	Person C	Person D	Person E
2	Person B	4	5	4	2	3	3	4	5	3	3	5	4	3	2	4	4	5	4		Person A	0	-1	-1	-1	-1
3	Person C	4	4	3	4	5	3	4	5	3	3	5	4	2	3	4	4	4	4		Person B	1	0	1	-1	-1
4	Person D	4	4	7	5	4	4	6	5	5	4	6	5	3	3	4	4	5	5		Person C	1	-1	0	-1	0
5	Person E	4	5	3	3	4	3	4	5	3	3	5	6	3	2	4	3	5	4		Person D	1	1	1	0	1
6																					Person E	1	1	0	-1	0
7
8																					Total Wins	4	2	2	0	1
9																					Total Losses	0	2	1	4	2
10																					Total Ties	0	0	1	0	1
11
Sheet2

Cell Formulas
Range	Formula
V1:Z1	V1	=TRANSPOSE(A1:A5)
U2:U6	U2	=A1:A5
V2:Z6	V2	=LET(a,XLOOKUP(V$1,$A$1:$A$5,$B$1:$S$5),b,XLOOKUP($U2,$A$1:$A$5,$B$1:$S$5),s,SUM(--(a<b))-SUM(--(a>b)),IF(s>0,1,IF(s<0,-1,0)))
V8:Z8	V8	=COUNTIF(V2:V6,1)
V9:Z9	V9	=COUNTIF(V2:V6,-1)
V10:Z10	V10	=COUNTIF(V2:V6,0)-1
Dynamic array formulas.

 

[Rock5150]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Person A	3	5	3	3	3	2	6	6	2	3	5	4	2	3	4	3	5	7			Person A	Person B	Person C	Person D	Person E
2	Person B	4	5	4	2	3	3	4	5	3	3	5	4	3	2	4	4	5	4		Person A	0	-1	-1	-1	-1
3	Person C	4	4	3	4	5	3	4	5	3	3	5	4	2	3	4	4	4	4		Person B	1	0	1	-1	-1
4	Person D	4	4	7	5	4	4	6	5	5	4	6	5	3	3	4	4	5	5		Person C	1	-1	0	-1	0
5	Person E	4	5	3	3	4	3	4	5	3	3	5	6	3	2	4	3	5	4		Person D	1	1	1	0	1
6																					Person E	1	1	0	-1	0
7
8																					Total Wins	4	2	2	0	1
9																					Total Losses	0	2	1	4	2
10																					Total Ties	0	0	1	0	1
11
Sheet2

Cell Formulas
Range	Formula
V1:Z1	V1	=TRANSPOSE(A1:A5)
U2:U6	U2	=A1:A5
V2:Z6	V2	=LET(a,XLOOKUP(V$1,$A$1:$A$5,$B$1:$S$5),b,XLOOKUP($U2,$A$1:$A$5,$B$1:$S$5),s,SUM(--(a<b))-SUM(--(a>b)),IF(s>0,1,IF(s<0,-1,0)))
V8:Z8	V8	=COUNTIF(V2:V6,1)
V9:Z9	V9	=COUNTIF(V2:V6,-1)
V10:Z10	V10	=COUNTIF(V2:V6,0)-1
Dynamic array formulas.

Thank you. For the LET formula, when I enter, it said it had too few arguments.

 

[Rock5150]

Thank you. For the LET formula, when I enter, it said it had too few arguments.

Never mind. I figured it out and this works. Thank you JGordon11!