Formula for football (Premier League) Predictions

m.amin.sh

New member
Joined
Aug 8, 2019
Messages
2
Reaction score
0
Points
0
Location
Asia
Excel Version(s)
2007
Hey fellas.
Me & my friends running a football prediction competition for English Premier League. I was wondering if there is a formula that could automatically compare actual results with predicted results and assign points.


The scoring system is as like this:

Correct Result: 7 points
Correct Result With Goal Difference: 5 points
Only Correct Winner: 3 Points
Wrong Result: 1 Points

Example:
Like Liverpool vs Norwich

Actual Result: 3-1

Player 1: 3-1 (7 Points)
Player 2: 2-0 or 4-2 or 5-3 or ... (5 Points)
Player 3: 1-0 or 2-1 or 3-0 or ... (3 Points)
Player 4: 0-0 or 0-1 or 1-3 or ... (1 Points)

View attachment Example.xlsx

Thank You so much!
 
Not elegant, but does the job

Code:
=IF(A2=actual_res,7,
IF(N(LEFT(A2,FIND("-",A2)-1)-MID(A2,FIND("-",A2)+1,2))=N(LEFT(actual_res,FIND("-",actual_res)-1)-MID(actual_res,FIND("-",actual_res)+1,2)),5,
IF(SIGN(N(LEFT(A2,FIND("-",A2)-1)-MID(A2,FIND("-",A2)+1,2)))=SIGN(N(LEFT(actual_res,FIND("-",actual_res)-1)-MID(actual_res,FIND("-",actual_res)+1,2))),3,1)))
 
Not elegant, but does the job

Code:
=IF(A2=actual_res,7,
IF(N(LEFT(A2,FIND("-",A2)-1)-MID(A2,FIND("-",A2)+1,2))=N(LEFT(actual_res,FIND("-",actual_res)-1)-MID(actual_res,FIND("-",actual_res)+1,2)),5,
IF(SIGN(N(LEFT(A2,FIND("-",A2)-1)-MID(A2,FIND("-",A2)+1,2)))=SIGN(N(LEFT(actual_res,FIND("-",actual_res)-1)-MID(actual_res,FIND("-",actual_res)+1,2))),3,1)))

thanks
I put it in but looks like it doesn't work for me.

could you sent me the excel you worked on?
 
I just put 3-1 in A1 and named the cell actual_res, then I put various scores in A2, 3-1, 4-2, 3-0, 2-2, 0-1.
 
As an LFC fan i wud b remiss to point out the Norwich score was 4-1, not 3-1! ;-) YNWA!
 
As an LFC fan i wud b remiss to point out the Norwich score was 4-1, not 3-1! ;-) YNWA!
Excellent! (from someone who has no interest whatsoever in football)

Try in cell J2 of your attachment:
Code:
=SUM(IF(SIGN($C2-$D2)=SIGN(H2-I2),2,0),IF(($C2-H2)=($D2-I2),2,0),IF(AND($C2=H2,$D2=I2),2,0),IF(COUNT(H2:I2)=2,1,0))
and copy down. You can then copy across to columns N,R,V & Z too.
Check it carefully because I may not have the logic quite right…

Winner: IF(SIGN($C2-$D2)=SIGN(H2-I2),2,0)
Goal Differrence: IF(($C2-H2)=($D2-I2),2,0)
Fully correct: IF(AND($C2=H2,$D2=I2),2,0)
For trying: IF(COUNT(H2:I2)=2,1,0)
 
Last edited:
Back
Top