Football prediction formula

[alsmonkeymagic]

Hi

I am trying to write a formula that will work out scores for me based on a set of predictions against foot ball scores - I have set out the table below. For each correct result, so for example you predict a 1-0 win and its a 2-1 win you get 1 point. For each correct result and score, so for example you predict a 1-0 win and its a 1-0 win you get 3 points. Also each week you can choose 1 game that you want to double your points on, so if you get the result right 2 points and a correct score and result 6 points. I just cannot seem to get my head around it, any help, please!!! I am using Excel 2016.

[TABLE="width: 724"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Actual Result[/TD]
[TD][/TD]
[TD]Prediction[/TD]
[TD][/TD]
[TD]Double points[/TD]
[TD]Points Scored[/TD]
[/TR]
[TR]
[TD]Bournemouth[/TD]
[TD]v[/TD]
[TD]Man Utd[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD]v[/TD]
[TD]Wolves[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Aston Villa[/TD]
[TD]v[/TD]
[TD]Liverpool[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 724"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

Welcome to Mr Excel

See if this does what you need, I've assumed that the same rules apply to a draw (1 point for predicting 2-2 against a result of 1-1, 3 points for correct score).

=IF(COUNT(D2:G2)<4,"",IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1))

There are 4 IF's in the formula, the 1st checks that the scores for both result and prediction have been entered to prevent points being awarded incorrectly in the event of a draw predicted on a game not yet played.
The 2nd IF checks if the predicted score is an exact match for the result. The 3rd checks checks the actual winner is the same as the predicted winner when the score is different. The last one doubles the points where applicable.

 

[alsmonkeymagic]

Thank you for the welcome and the reply, I have used the formula but I am only getting a blank cell. I assume it would not matter what the cell format is?

Welcome to Mr Excel

See if this does what you need, I've assumed that the same rules apply to a draw (1 point for predicting 2-2 against a result of 1-1, 3 points for correct score).

=IF(COUNT(D2:G2)<4,"",IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1))

There are 4 IF's in the formula, the 1st checks that the scores for both result and prediction have been entered to prevent points being awarded incorrectly in the event of a draw predicted on a game not yet played.
The 2nd IF checks if the predicted score is an exact match for the result. The 3rd checks checks the actual winner is the same as the predicted winner when the score is different. The last one doubles the points where applicable.

 

[jasonb75]

The format does make a difference, I noticed in your example that the scores were aligned to the left which I thought was just the way the forum software had formatted the copy and paste of your table. However, it also suggests that the numbers in those cells are formatted as text, in which case the first part of the formula would not count them correctly.

It should work by removing the 1st IF, however this could potentially mean that incorrect points are awarded with a predicted draw.

=IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1)

I would suggest not using a text format for cells that contain numbers, it is is rarely necessary and can break even the most simple formulas.