Scoresheet help

[spoonful]

Hi! I don't really have a solid background in Excel, so please bear with me. I'm planning to host a sports ranking prediction game and I'm currently having some difficulty finding the appropriate Excel function/s for my scoresheet.

The point system goes like this:

2 points - correctly guessing the exact placement of a player
1 point - guessing the rank to be ±1 place away from the exact placement (Example: Westbrook got a No. 3 ranking. Prediction game participant predicted him to be No. 2 or 4.)
0.5 point - guessing the rank to be ±2 places away from the exact placement (Example: Curry got a No. 7 ranking. Prediction game participant predicted him to be No. 5 or 9.)

Let's assume this is the Top 10 in order:

1. LeBron James
2. Kevin Durant
3. Anthony Davis
4. Stephen Curry
5. Kawhi Leonard
6. Chris Paul
7. Russell Westbrook
8. Karl-Anthony Towns
9. Paul George
10. Klay Thompson
11. Jimmy Butler*
12. James Harden*

*Note: Only the Top 10 is up for prediction.


These are the sample entries encoded in an Excel scoresheet matrix:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
James	Leonard	Durant	Towns	Westbrook	Leonard	Paul	Curry	Towns	Harden
Durant	Westbrook	Paul	James	Curry	Towns	Leonard	George	Thompson	Davis
Curry	Durant	James	Leonard	Curry	Paul	Davis	Westbrook	Butler	Towns
James	Towns	Paul	Curry	Harden	Durant	Westbrook	Thompson	George	Leonard
Davis	James	Towns	Butler	Durant	Curry	Westbrook	Thompson	Leonard	George

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Participants[/TD]
[TD="bgcolor: #C4D79B, align: center"]ENTRIES[/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="align: center"]Exact Rank Score[/TD]
[TD="align: center"]±1 Deviation Score[/TD]
[TD="align: center"]±2 Deviation Score[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #B8CCE4, align: center"]1[/TD]
[TD="bgcolor: #B8CCE4, align: center"]2[/TD]
[TD="bgcolor: #B8CCE4, align: center"]3[/TD]
[TD="bgcolor: #B8CCE4, align: center"]4[/TD]
[TD="bgcolor: #B8CCE4, align: center"]5[/TD]
[TD="bgcolor: #B8CCE4, align: center"]6[/TD]
[TD="bgcolor: #B8CCE4, align: center"]7[/TD]
[TD="bgcolor: #B8CCE4, align: center"]8[/TD]
[TD="bgcolor: #B8CCE4, align: center"]9[/TD]
[TD="bgcolor: #B8CCE4, align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Red[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Blue[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Yellow[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Green[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Orange[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sample Entries Matrix​

Excel 2010

	A	B
LeBron James
Kevin Durant
Anthony Davis
Stephen Curry
Kawhi Leonard
Chris Paul
Russell Westbrook
Karl-Anthony Towns
Paul George
Klay Thompson
Jimmy Butler*
James Harden*
*Note: Only the Top 10 is up for prediction.

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Hypothetical Top 12
​

Your help will be very much appreciated. Suggestions to better execute the tabulation would be greatly welcome. Thank you very much!

 

[AlanY]

this solution use a helper table in rows 22-26, also with players' surnames in C1-C12

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	LeBron James	James
2	2	Kevin Durant	Durant
3	3	Anthony Davis	Davis
4	4	Stephen Curry	Curry
5	5	Kawhi Leonard	Leonard
6	6	Chris Paul	Paul
7	7	Russell Westbrook	Westbrook
8	8	Karl-Anthony Towns	Towns
9	9	Paul George	George
10	10	Klay Thompson	Thompson
11	11	Jimmy Butler*	Butler*
12	12	James Harden*	Harden*
13
14		Participants	ENTRIES										Exact Rank Score	1 Deviation Score	2 Deviation Score	Total
15			1	2	3	4	5	6	7	8	9	10
16		Red	James	Leonard	Durant	Towns	Westbrook	Leonard	Paul	Curry	Towns	Harden	1	4	1	6.5
17		Blue	Durant	Westbrook	Paul	James	Curry	Towns	Leonard	George	Thompson	Davis	0	4	2	5
18		Yellow	Curry	Durant	James	Leonard	Curry	Paul	Davis	Westbrook	Butler	Towns	2	3	2	8
19		Green	James	Towns	Paul	Curry	Harden	Durant	Westbrook	Thompson	George	Leonard	4	0	1	8.5
20		Orange	Davis	James	Towns	Butler	Durant	Curry	Westbrook	Thompson	Leonard	George	1	2	3	5.5
21
22			1	5	2	8	7	5	6	4	8	0
23			2	7	6	1	4	8	5	9	10	3
24			4	2	1	5	4	6	3	7	0	8
25			1	8	6	4	0	2	7	10	9	5
26			3	1	8	0	2	4	7	10	5	9
Sheet1

Cell Formulas
Range	Formula
P16	=2*M16+N16+0.5*O16
C22	=IFERROR(INDEX($A$1:$A$10,MATCH(C16,$C$1:$C$10,0)),0)
M16	{=SUM(IF($A$1:$A$10=TRANSPOSE($C22:$L22),1))}
N16	{=SUM(IF(ABS($A$1:$A$10-TRANSPOSE($C22:$L22))=1,1))}
O16	{=SUM(IF(ABS($A$1:$A$10-TRANSPOSE($C22:$L22))=2,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RoryA]

Cross-posted here: Scoresheet help

Please take a minute to read the forum rules, especially as they relate to cross-posting, and abide by them in future. Thanks.