# Thread: Formula for football (Premier League) Predictions

1. ## Formula for football (Premier League) Predictions

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)

Example.xlsx

Thank You so much!  Reply With Quote

2. 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)))```  Reply With Quote

3. Originally Posted by Bob Phillips 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?  Reply With Quote

4. 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.  Reply With Quote

5. As an LFC fan i wud b remiss to point out the Norwich score was 4-1, not 3-1! ;-) YNWA!  Reply With Quote

6. Originally Posted by Josephking1388@gmail.com 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)  Reply With Quote

#### Tags for this Thread

formula, predict, score #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•