Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Soccer Formula

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Location
    Scotland
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Soccer Formula

    Hi all, Looking for some help with a formula that allocates 3 points for a predicting Home Win, Away Win or Draw, but also allocates 6 points should the person predict the correct score, in the instance below Susan predicted 3-3, which was correct, in turn gets a total of 9 points, 3 for predicting draw and 6 for correct score. Much obliged JJSB

    Name Matches Predicted Score Actual Score Points
    John Russia Saudi Arabia 1 0 3 3 0
    Jim Russia Saudi Arabia 1 0 0
    Peter Russia Saudi Arabia 2 0 0
    Mary Russia Saudi Arabia 3 0 0
    Susan Russia Saudi Arabia 3 3 0
    Paul Russia Saudi Arabia 2 1 0
    Mel Russia Saudi Arabia 3 2 0
    Connor Russia Saudi Arabia 4 2 0
    Last edited by jayjaysb; May 15th, 2018 at 04:41 AM. Reason: typo

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,470
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Try:

    =IF(AND(D2<>"",E2<>"",G2<>"",H2<>""),IF(OR(AND(D2 < E2,G2 < H2),AND(D2=E2,G2=H2),AND(D2>E2,G2>H2)),IF(AND(D2=G2,E2=H2),9,3),0),0)
    Last edited by steve the fish; May 15th, 2018 at 04:51 AM.

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Location
    Scotland
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Hi Steve, No it's just populating each box with a zero, Thanks JJSB
    Last edited by jayjaysb; May 15th, 2018 at 05:03 AM.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,470
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Yours has empty columns in your opening post! Anyway:

    =IF(AND(D2<>"",E2<>"",F2<>"",G2<>""),IF(OR(AND(D2 < E2,F2 < G2),AND(D2=E2,F2=G2),AND(D2>E2,F2>G2)),IF(AND(D2=F2,E2=G2),9,3),0),0)

  5. #5
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    621
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Quote Originally Posted by jayjaysb View Post
    Hi all, Looking for some help with a formula that allocates 3 points for a predicting Home Win, Away Win or Draw, but also allocates 6 points should the person predict the correct score, in the instance below Susan predicted 3-3, which was correct, in turn gets a total of 9 points, 3 for predicting draw and 6 for correct score. Much obliged JJSB

    Name Matches Predicted Score Actual Score Points
    John Russia Saudi Arabia 1 0 3 3 0
    Jim Russia Saudi Arabia 1 0 0
    Peter Russia Saudi Arabia 2 0 0
    Mary Russia Saudi Arabia 3 0 0
    Susan Russia Saudi Arabia 3 3 0
    Paul Russia Saudi Arabia 2 1 0
    Mel Russia Saudi Arabia 3 2 0
    Connor Russia Saudi Arabia 4 2 0
    As points column starts from H2 =IF(F2G2,IF(D2>E2,IF(AND(F2=D2,G2=E2),9,3),0)),IF(D2=E2,IF(AND(F2=D2,G2=E2),9,3),0))


    I hope it will work.

  6. #6
    New Member
    Join Date
    May 2018
    Location
    GB
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Here is my own solution, assuming your table's header row is 1 and your score columns are D, E, F and G:

    = INT(AND(D2<>"",E2<>"",F2<>"",G2<>""))*(6*INT(AND(D2 = F2, E2 = G2)) + 3*INT(OR(AND(D2>E2,F2>G2),AND(D2

  7. #7
    New Member
    Join Date
    May 2018
    Location
    GB
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Quote Originally Posted by arpd123 View Post
    Here is my own solution, assuming your table's header row is 1 and your score columns are D, E, F and G:

    = INT(AND(D2<>"",E2<>"",F2<>"",G2<>""))*(6*INT(AND(D2 = F2, E2 = G2)) + 3*INT(OR(AND(D2>E2,F2>G2),AND(D2
    This post (and the formula) got truncated somehow. When I clicked "reply with quote", it did not have the closing quote tag, either and I had to add it in manually. I don't have any editing privleges, unfortunately, as I assume I do not have a high enough post count.

  8. #8
    Board Regular
    Join Date
    Nov 2011
    Location
    Scotland
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Steve, I am almost there, I input the formula into H2 and copied down, showed all zeros, but when I changed John's score to 3-3 it gave him 9 points (correctly) and then it changed Susan's to 9. Thanks JJSB

  9. #9
    New Member
    Join Date
    Apr 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Try this:

    =IF(AND((F2=D2),(E2=G2)),9,IF(OR(AND(F2>G2,D2>E2),AND(G2>F2,E2>D2)),3,IF(AND((F2=G2),(E2=D2)),3,0)))

  10. #10
    Board Regular
    Join Date
    Nov 2011
    Location
    Scotland
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Soccer Formula

    Hi Final, that works, but only allocates 3 points for correct score and not 6, if you get me, thanks JJSB

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com