+ Reply to Thread
Results 1 to 5 of 5

Football (soccer) predictions formula

  1. #1
    Registered User
    Join Date
    08-11-2008
    Location
    U.K.
    Posts
    2

    Football (soccer) predictions formula

    I'm trying to write a formula that will automatically take a group of football (soccer) scores, compare them with a set of predictions, and then allocate points according to how close the predictions are to the actual scores.

    E.g.
    Manchester United 3 - 0 Arsenal
    Prediction: 4 - 1
    Points: 1

    I've found a formula online that works for most scores; the correct score (e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect score: 1

    The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))

    Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.

    Something like =IF(A3:D3) are equal, 4,0

    Below are a few examples of what I mean:
    (Result) (Prediction) (Points)
    A B C D E
    3 - 3 3 - 3 4
    2 - 1 3 - 1 1
    1 - 0 1 - 0 3
    3 - 3 2 - 2 1

    Apologies in advance if I haven't explained this clearly enough.

    Thanks

    Will

  2. #2
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Smile

    Hi Will,
    Try the following formula:

    =IF(AND(A3=B3,A3=C3,A3=D3),4,IF(A3&B3=C3&D3,3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))

    this returns the number of points you required in col E



    [Below are a few examples of what I mean:
    (Result) (Prediction) (Points)
    A B C D E
    3 - 3 3 - 3 4
    2 - 1 3 - 1 1
    1 - 0 1 - 0 3
    3 - 3 2 - 2 1 ]


    Regards Howard

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Here's one way

    =IF(COUNT(A3:D3)=4,IF((A3=C3)*(B3=D3),IF(A3=B3,4,3),IF(SIGN(A3-B3)=SIGN(C3-D3),1)))+0

  4. #4
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Smile

    Hi DLL,
    I'm amazed! SIGN where did that one come from? ( shakes head in disbelief )
    you use functions I've never heard of!
    brilliant! I think you should give tutorials to lesser mortals like me!
    Best regards Howard

  5. #5
    Registered User
    Join Date
    08-11-2008
    Location
    U.K.
    Posts
    2

    Thanks

    Thanks guys, both are working so far.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1