Results 1 to 7 of 7

Thread: Formula for football (Premier League) Predictions

  1. #1
    Neophyte m.amin.sh's Avatar
    Join Date
    Aug 2019
    Location
    Asia
    Posts
    2
    Articles
    0
    Excel Version
    2007

    Exclamation Formula for football (Premier League) Predictions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,672
    Articles
    0
    Excel Version
    O365
    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)))

  3. #3
    Neophyte m.amin.sh's Avatar
    Join Date
    Aug 2019
    Location
    Asia
    Posts
    2
    Articles
    0
    Excel Version
    2007
    Quote Originally Posted by Bob Phillips View Post
    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?

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,672
    Articles
    0
    Excel Version
    O365
    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.

  5. #5
    Neophyte Josephking1388@gmail.com's Avatar
    Join Date
    Aug 2019
    Posts
    4
    Articles
    0
    Excel Version
    Mac version 15.13.3
    As an LFC fan i wud b remiss to point out the Norwich score was 4-1, not 3-1! ;-) YNWA!

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Josephking1388@gmail.com View Post
    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 by p45cal; 2019-08-15 at 07:08 PM.

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,672
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by p45cal View Post
    Excellent! (from someone who has no interest whatsoever in football)
    Then you must be a Man Utd fan

Tags for this Thread

Posting Permissions

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