6 Nations tournament score predictor

[rugby_nut]

Hello,
I have been struggling to create a 6 Nations rugby score predictor as a bit of fun at the rugby club for next years tournament. They would need to predict the scores for all matches before the tournament started. Get points deducted or added for the points difference between each result and points deducted or added for predicting the correct result, with bonus points for the exact score or closest to it. Extra points for winning each round. Or something similar along those lines.

Would any of you kind people have a template they could share please?

 

[RobP]

Hi, here's a starter for you, done for O365. (you should update your profile so people here know what version of Excel you are running for future).

If you don't have O365, you'll likely have to split the LET() functions out into individual columns across the right hand side individually. The score values can be changed freely in the Cells if you want to make them more / less than my example. formulas will update automatically. You can add as many names to the list of players as you have signed up, just be sure to copy the formulas down as you go.

The caveat here is the Bonus Column Y, which needs to know the range of all players in the league - so you'll need to adjust it when you add someone new ~(unless you use "insert" row within the data somewhere, which should update it automatically.

"Score Predictions" heading was actually written across both columns E & F (but the XL2BB addin seems to have changed the formatting slightly here - but you'll get the gist.

Good luck

6 nations.xlsx
	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		RULES:	5	points awarded for correct WIN/LOSE								4	Bonus Points for Winners/joint top each Round
2			10	points awarded for correct score line both sides
3			5	points awarded for correct scoreline one side
4			3	points awarded for TOTAL points scored by both teams
5			2	points for being within 3 on either score (2 pts max possible)
6
7	Players				Wales	Italy			Wales	France			Wales	England			Wales	Scotland			Wales	Ireland
8	First	Last		Actual Scores:	11	12			18	15			24	16			33	28			20	21
9						Score Predictions	Points			Score Predictions	Points			Score Predictions	Points			Score Predictions	Points			Score Predictions	Points		Bonus	Totals
10	Gareth	Edwards			11	12	20		11	12	2				0				0				0		4	26
11	Eddie	Butler			50	22	0		50	22	5		14	12	5		23	25	2		22	20	2		12	26
12	Graham	Price			32	16	0		32	16	7				0				0				0		4	11
13	JPR	Williams			13	8	2		13	8	5				0				0				0		0	7
14	Ritchie	Collins			18	21	5		18	21	7				0				0				0		4	16
15
16
Sheet1

Cell Formulas
Range	Formula
Y10:Y14	Y10	=LET(round1,IF(G10 = MAX(G$10:G$14),$L$1,0),round2,IF(K10 = MAX(K$10:K$14),$L$1,0),round3,IF(O10 = MAX(O$10:O$14),$L$1,0),round4,IF(S10 = MAX(S$10:S$14),$L$1,0),round5,IF(W10 = MAX(W$10:W$14),$L$1,0),round1+round2+round3+round4+round5)
Z10:Z14	Z10	=SUM(G10,K10,O10,S10,W10,Y10)
G10:G14,W10:W14,S10:S14,O10:O14,K10:K14	G10	=LET(winlose,IF(OR(AND(E$8>F$8, E10>F10),AND(E$8<F$8,E10<F10)),$C$1,0),score,IF(AND(E10=E$8,F10=F$8),$C$2,IF(OR(E10=E$8,F10=F$8),$C$3,0)),total,IF(E10+F10 = E$8+F$8,$C$4,0),delta,IF(OR(ABS(E$8-E10)<4,ABS(F$8-F10)<4),$C$5,0),delta+total+winlose+score)