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
1	Participants	ENTRIES										Exact Rank Score	±1 Deviation Score	±2 Deviation Score	Total
2		1	2	3	4	5	6	7	8	9	10
3	Red	James	Leonard	Durant	Towns	Westbrook	Leonard	Paul	Curry	Towns	Harden
4	Blue	Durant	Westbrook	Paul	James	Curry	Towns	Leonard	George	Thompson	Davis
5	Yellow	Curry	Durant	James	Leonard	Curry	Paul	Davis	Westbrook	Butler	Towns
6	Green	James	Towns	Paul	Curry	Harden	Durant	Westbrook	Thompson	George	Leonard
7	Orange	Davis	James	Towns	Butler	Durant	Curry	Westbrook	Thompson	Leonard	George

<tbody>
</tbody>

Sample Entries Matrix​

Excel 2010

	A	B
1	1	LeBron James
2	2	Kevin Durant
3	3	Anthony Davis
4	4	Stephen Curry
5	5	Kawhi Leonard
6	6	Chris Paul
7	7	Russell Westbrook
8	8	Karl-Anthony Towns
9	9	Paul George
10	10	Klay Thompson
11	11	Jimmy Butler*
12	12	James Harden*
13
14
15
16		*Note: Only the Top 10 is up for prediction.

<tbody>
</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.