Football Prediction Formula

[S-K-99]

Hello all,

Anyone got time to help with 2 formula's for my new prediction League I am running for my friends, I would rather not have to input all the values by hand every week.

What I would like is for someone to provide me if at all possible with the 2 full formula's if they would be so kind? I am an absolute novice with Excel and would greatly appreciate all the help I can get.

Formula 1

What I would like is for when I enter the results in column F4 & G4​ (Highlighted in Red), it will automatically populate K4 (Highlighted in Green) using the prediction score rules listed below the table. However if no results are entered in F4 & G4, K4 shows a 0.

Formula 2

I would like H15 (Highlighted in Purple) to automatically populate to show how many goals the person has scored using the points to goals ratio shown below.

I really hope someone can help me

Thanks in advance

SK

	Game Sheet
	Game	Matches			Score	Browny Browny
	1	Man Utd	v	Swansea	2	1	2	1	J	10
	2	Leicester	v	Everton	2	2	1	1		3
	3	QPR	v	Hull	3	1	1	3		-2
	4	Stoke	v	Aston Villa	1	0	1	0		5
	5	West Brom	v	Sunderland			1	0		0
	6	West Ham	v	Spurs			0	1		0
	7	Arsenal	v	Crystal Palace			2	0		0
	8	Liverpool	v	Southampton			2	0		0
	9	Newcastle	v	Man City			0	2		0
	10	Burnley	v	Chelsea			0	3		0
	Total Points Scored		16
	Goals Scored		4
	Points System					0 to 5 pts = 0 goals		18,19,20 pts = 5 goals
	Score Exactly correct = 5 Points				6,7,8 pts = 1 goals		21,22,23 pts = 6 goals
	Draw (Except for exact score) = 3 Points			9,10,11 pts = 2 goals	24,25 pts = 7 goals
	Correct Result (Except for exact score) = 1 Point			12,13,14 pts = 3 goals	26,27 pts = 8 goals
	Incorrect Result = -2 Points				15,16,17 pts = 4 goals	28,29 pts = 9 goals
	* Joker doubles the points on nominated game									30 + pts = 10 goals

<tbody>
</tbody>

 

[StephenCrump]

Ahh, real football. Welcome to MrExcel.

Try these formulae in the cells highlighted:

K4: =IF(AND(F4="",G4=""),0,IF(J4="J",2,1)*IF(AND(F4=H4,G4=I4),5,IF(AND(F4=G4,H4=I4),3,IF(SIGN(F4-G4)=SIGN(H4-I4),1,-2)))) and copy down to K5, K6 etc

H15: =VLOOKUP(H14,M3:N13,2,TRUE)

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2													Points	Goals
3		Game	Matches										0	0
4		1	Man Utd	v	Swansea	2	1	2	1	J	10		6	1
5		2	Leicester	v	Everton	2	2	1	1		3		9	2
6		3	QPR	v	Hull	3	1	1	3		-2		12	3
7		4	Stoke	v	Aston Villa	1	0	1	0		5		15	4
8		5	West Brom	v	Sunderland			1	0		0		18	5
9		6	West Ham	v	Spurs			0	1		0		21	6
10		7	Arsenal	v	Crystal Palace			2	0		0		24	7
11		8	Liverpool	v	Southampton			2	0		0		26	8
12		9	Newcastle	v	Man City			0	2		0		28	9
13		10	Burnley	v	Chelsea			0	3		0		30	10
14			Total Points Scored					16
15			Goals Scored					4

<tbody>
</tbody>

Sheet1​

 

[S-K-99]

Yes, Yes, Yes...... you are the man!!

Thank you so much mate, it works a treat!!

Regards,

SK

 

[StephenCrump]

Thanks, glad to help.