Excel Formula for Ladder Predictions

[michaeljakes13]

Hi all,

I was hoping someone would be able to assist me with creating a formula for the following - any help would be greatly appreciated!

I'm trying to create a formula to spit out a value in Row 25 which compares the differences between the actual Premier League Table (Column B) and the predictions we have all entered (Column C-F).

The scoring system is so that every place a team is away from where they are currently (i.e. 5 places between Chelsea's current placing Harry's choice of Chelsea's position = 5 points, Man City = 10 poimts). I would like to calculate the total sum of all differences of predictions (a high score is bad and a low score is good!)

I hope that makes sense!

Appreciated,
Michael

 

[Habtest]

Hi Michael, how about:

Book1
	A	B	C	D	E	F
1
2
3	Position	Current	HB	MJ	MM	ZG
4	1	A	T	H	A	J
5	2	B	A	G	E	K
6	3	C	B	R	N	T
7	4	D	C	L	L	R
8	5	E	D	P	H	I
9	6	F	E	F	M	P
10	7	G	F	K	S	L
11	8	H	G	S	B	C
12	9	I	H	D	J	F
13	10	J	I	E	C	M
14	11	K	J	O	F	G
15	12	L	K	M	G	N
16	13	M	L	J	Q	D
17	14	N	M	A	D	S
18	15	O	N	I	K	Q
19	16	P	O	B	O	O
20	17	Q	P	N	P	B
21	18	R	Q	C	T	H
22	19	S	R	Q	I	E
23	20	T	S	T	R	A
24
25			38	132	102	160
Sheet1

Cell Formulas
Range	Formula
C25:F25	C25	=SUM(ABS(MATCH(C$4:C$23,$B$4:$B$23,0)-MATCH(C$4:C$23,C$4:C$23,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[michaeljakes13]

Thank you so much!!!!!!!!!!!! I couldnt be any more thankful

 

[Habtest]

Glad to help and thanks for the feedback