Score Prediction Template

[DavidMoran]

I am trying to make a sports prediction quiz. I would like the scoring to be

Margin	Points
Draw	40
Winner	20
Exact	10
1	9
2	8
3	7
4	6
5	5
6	4
7	3
8	2
9	1

<tbody>
</tbody>

The selection will be pick, winner, or draw and the points margin of victory e.g

	Prediction	Margin
Joe	Team 1	3
Pat	Draw	0
Mary	Team 2	5

<tbody>
</tbody>

Example: the Team 1 beat Team 2 by 3 points. Therefore Joe gets 20 points for picking winner and an extra 3 for correct points margin

Example 2: The teams draw, therefore Pat gets 40 points

Example 3, Team 2 wins by 4. Therefore Mary gets 20 point only

Many thanks

 

[AlanY]

what is the significance of the RED below?

and if correctly picked the margin, say 5 pts, what's the extra?

I am trying to make a sports prediction quiz. I would like the scoring to be

Margin	Points
Draw	40
Winner	20
Exact	10
1	9
2	8
3	7
4	6
5	5
6	4
7	3
8	2
9	1

<tbody>
</tbody>

The selection will be pick, winner, or draw and the points margin of victory e.g

	Prediction	Margin
Joe	Team 1	3
Pat	Draw	0
Mary	Team 2	5

<tbody>
</tbody>

Example: the Team 1 beat Team 2 by 3 points. Therefore Joe gets 20 points for picking winner and an extra 3 for correct points margin

Example 2: The teams draw, therefore Pat gets 40 points

Example 3, Team 2 wins by 4. Therefore Mary gets 20 point only

Many thanks

 

[DavidMoran]

If a team wins by 5 and they correctly pick 5, they get an extra 5 on top of the 20 for picking the correct winning team. If a team wins by 2 and they correctly pick 2, they get an extra 8 on top of the 20. In essence, the tighter the margin the higher the extra points
Thanks

what is the significance of the RED below?

and if correctly picked the margin, say 5 pts, what's the extra?

 

[AlanY]

If a team wins by 5 and they correctly pick 5, they get an extra 5 on top of the 20 for picking the correct winning team. If a team wins by 2 and they correctly pick 2, they get an extra 8 on top of the 20. In essence, the tighter the margin the higher the extra points
Thanks

I am trying to make a sports prediction quiz. I would like the scoring to be

Margin	Points
Draw	40
Winner	20
Exact	10
1	9
2	8
3	7
4	6
5	5
6	4
7	3
8	2
9	1

<tbody>
</tbody>

The selection will be pick, winner, or draw and the points margin of victory e.g

	Prediction	Margin
Joe	Team 1	3
Pat	Draw	0
Mary	Team 2	5

<tbody>
</tbody>

Example: the Team 1 beat Team 2 by 3 points. Therefore Joe gets 20 points for picking winner and an extra 3 for correct points margin

Example 2: The teams draw, therefore Pat gets 40 points

Example 3, Team 2 wins by 4. Therefore Mary gets 20 point only

Many thanks

ok, just to clarify, shouldn't Joe gets 20 + 7 points?

what about the 10 points for Exact?

 

[DavidMoran]

ok, just to clarify, shouldn't Joe gets 20 + 7 points?

Excellent spot, apologies for the confusion

 

[AlanY]

what about the 10 points for Exact?

I am trying to make a sports prediction quiz. I would like the scoring to be

Margin	Points
Draw	40
Winner	20
Exact	10
1	9
2	8
3	7
4	6
5	5
6	4
7	3
8	2
9	1

<tbody>
</tbody>

The selection will be pick, winner, or draw and the points margin of victory e.g

	Prediction	Margin
Joe	Team 1	3
Pat	Draw	0
Mary	Team 2	5

<tbody>
</tbody>

Example: the Team 1 beat Team 2 by 3 points. Therefore Joe gets 20 points for picking winner and an extra 3 for correct points margin

Example 2: The teams draw, therefore Pat gets 40 points

Example 3, Team 2 wins by 4. Therefore Mary gets 20 point only

Many thanks

 

[AlanY]

try this, not taking into the account of the 10 points for Exact

Excel 2013/2016
	A	B	C	D
1	Margin	Points
2	Draw	40
3	Winner	20
4	Exact	10
5	1	9
6	2	8
7	3	7
8	4	6
9	5	5
10	6	4
11	7	3
12	8	2
13	9	1
14
15	Result
16	Team 1	7
17	Team 2	4
18
19		Prediction	Margin	Points
20	Joe	Team 1	3	27
21	Pat	Draw	0	0
22	Mary	Team 2	5	0
Sheet1

Cell Formulas
Range	Formula
D20	=IFERROR(IF(LOOKUP(B20,$A$16:$A$17,$B$16:$B$17)>MIN($B$16:$B$17), 20+IF(ABS($B$16-$B$17)=C20,10-C20,0),0),IF($B$16=$B$17,40,0))

 

[DavidMoran]

try this, not taking into the account of the 10 points for Exact

Excel 2013/2016

	A	B	C	D
1	Margin	Points
2	Draw	40
3	Winner	20
4	Exact	10
5	1	9
6	2	8
7	3	7
8	4	6
9	5	5
10	6	4
11	7	3
12	8	2
13	9	1
14
15	Result
16	Team 1	7
17	Team 2	4
18
19		Prediction	Margin	Points
20	Joe	Team 1	3	27
21	Pat	Draw	0	0
22	Mary	Team 2	5	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D20 =IFERROR(IF(LOOKUP(B20,$A$16:$A$17,$B$16:$B$17)>MIN($B$16:$B$17), 20+IF(ABS($B$16-$B$17)=C20,10-C20,0),0),IF($B$16=$B$17,40,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you
If it is exact, its 20 for the win and the extra 10 for exact points margin

 

[AlanY]

ok, but your example dont pick the scores, just margin.

Thank you
If it is exact, its 20 for the win and the extra 10 for exact points margin

The selection will be pick, winner, or draw and the points margin of victory e.g

	Prediction	Margin
Joe	Team 1	3
Pat	Draw	0
Mary	Team 2	5

<tbody>
</tbody>

 

[AlanY]

another way with scores picked

Excel 2013/2016
	A	B	C	D
1		Result
2		Team 1	Team 2
3		2	7
4
5		Prediction
6		Team 1	Team 2	Points
7	Joe	4	0	0
8	Pat	2	2	0
9	Mary	3	8	25
Sheet1

Cell Formulas
Range	Formula
D7	=IF(AND(B7=C7,$B$3=$C$3),40,IF(OR(AND(B7>C7,$B$3>$C$3),AND(C7>B7,$C$3>$B$3)),20,0)) +IF(AND(B7=$B$3,C7=$C$3),10,IF(AND((B7-C7)=($B$3-$C$3),B7<>C7),10-ABS(B7-C7),0))

Excel 2013/2016
	A	B	C	D
1		Result
2		Team 1	Team 2
3		2	2
4
5		Prediction
6		Team 1	Team 2	Points
7	Joe	4	0	0
8	Pat	2	2	50
9	Mary	3	8	0
Sheet1

Cell Formulas
Range	Formula
D7	=IF(AND(B7=C7,$B$3=$C$3),40,IF(OR(AND(B7>C7,$B$3>$C$3),AND(C7>B7,$C$3>$B$3)),20,0)) +IF(AND(B7=$B$3,C7=$C$3),10,IF(AND((B7-C7)=($B$3-$C$3),B7<>C7),10-ABS(B7-C7),0))

Excel 2013/2016
	A	B	C	D
1		Result
2		Team 1	Team 2
3		5	2
4
5		Prediction
6		Team 1	Team 2	Points
7	Joe	4	1	27
8	Pat	2	2	0
9	Mary	3	8	0
Sheet1

Cell Formulas
Range	Formula
D7	=IF(AND(B7=C7,$B$3=$C$3),40,IF(OR(AND(B7>C7,$B$3>$C$3),AND(C7>B7,$C$3>$B$3)),20,0)) +IF(AND(B7=$B$3,C7=$C$3),10,IF(AND((B7-C7)=($B$3-$C$3),B7<>C7),10-ABS(B7-C7),0))

Excel 2013/2016
	A	B	C	D
1		Result
2		Team 1	Team 2
3		5	1
4
5		Prediction
6		Team 1	Team 2	Points
7	Joe	4	1	20
8	Pat	2	2	0
9	Mary	1	5	0
Sheet1

Cell Formulas
Range	Formula
D7	=IF(AND(B7=C7,$B$3=$C$3),40,IF(OR(AND(B7>C7,$B$3>$C$3),AND(C7>B7,$C$3>$B$3)),20,0)) +IF(AND(B7=$B$3,C7=$C$3),10,IF(AND((B7-C7)=($B$3-$C$3),B7<>C7),10-ABS(B7-C7),0))