+ Reply to Thread
Results 1 to 12 of 12

Formula required to calculate points for football results prediction

  1. 08-02-2009, 11:32 AM #1
    Jen1979
    Jen1979 is offline
    Registered User
    Join Date
    08-02-2009
    Location
    Watford, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Formula required to calculate points for football results prediction

    Hi there

    I'm creating an (English) football predictions competition for me and my family.

    One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.

    The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.

    Please can someone tell me what I need to write? The online Microsoft Excel 2007 help only confused me further! Please see the example spreadsheet attached; I've added examples and explanations of what the scores should be...

    I can offer PowerPoint assistance in return; Excel is not my area of expertise!

    Many thanks
    Jenny
    Attached Files Attached Files
    Last edited by Jen1979; 08-02-2009 at 12:56 PM. Reason: amend to more appropriate title
    Reply With QuoteRegister To Reply
  2. 08-02-2009, 12:21 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Another Sports Result problem

    =if(and(b3=e3,d3=f3),3,if(or(e3-f3=b3-d3,and(e3>f3,b3>d3),and(f3>e3,d3>b3)),1,0))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 08-02-2009, 12:22 PM #3
    qff
    qff is offline
    Registered User
    Join Date
    10-04-2008
    Location
    uk
    Posts
    26

    Re: Another Sports Result problem

    Hi

    I think this formula should do what you need.

    This checks for the correct score, then looks to see if the prediction was a Home win, then an Away win and finally a draw to give 1pt for the correct result.


    Please Login or Register  to view this content.

    good luck
    qff
    Reply With QuoteRegister To Reply
  4. 08-02-2009, 12:27 PM #4
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Another Sports Result problem

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Reply With QuoteRegister To Reply
  5. 08-02-2009, 12:35 PM #5
    Jen1979
    Jen1979 is offline
    Registered User
    Join Date
    08-02-2009
    Location
    Watford, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Another Sports Result problem

    Sorry I have been rightly told off - I will amend the title!

    Many thanks to Martindwilson and qff for your quick response. Martin, yours worked very well thank you so much. I won't attempt to try and understand it, but CTRL+C/CTRL+V is my new best friend...

    Qff, for some reason the formula you provided did not calculate the correct points for a correct away win prediction (although it works for a draw prediction and a home win prediction) - but who am I to pick holes!

    Cheers
    Jenny
    Reply With QuoteRegister To Reply
  6. 08-02-2009, 12:48 PM #6
    Jen1979
    Jen1979 is offline
    Registered User
    Join Date
    08-02-2009
    Location
    Watford, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula required to calculate points for football results prediction

    Hi again,

    another quick question - Martindwilson, with the formula you sent earlier, when the home or away predictions cell is blank the points column is showing up with 3 points. Can you tell me what I need to add to the formula in order to have blank cells equalling 0?

    Jen
    Reply With QuoteRegister To Reply
  7. 08-02-2009, 12:49 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula required to calculate points for football results prediction

    Just for chuckles, another tack with no IF/THEN in it...in G3:

    =((E3 = B3) * (F3 = D3) * 2) + ((E3 > F3) * (B3 > D3)) + ((F3 >= E3) * (D3 >= B3))

    Anyway, if your issue is resolved, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  8. 08-02-2009, 01:00 PM #8
    Jen1979
    Jen1979 is offline
    Registered User
    Join Date
    08-02-2009
    Location
    Watford, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula required to calculate points for football results prediction

    thanks for all your help, have changed to [SOLVED]
    Reply With QuoteRegister To Reply
  9. 08-02-2009, 01:07 PM #9
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula required to calculate points for football results prediction

    well away correct win works fine for me
    see attached with ammended formula for blanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 08-02-2009, 01:17 PM #10
    Jen1979
    Jen1979 is offline
    Registered User
    Join Date
    08-02-2009
    Location
    Watford, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula required to calculate points for football results prediction

    thanks again!
    Reply With QuoteRegister To Reply
  11. 08-02-2009, 07:01 PM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula required to calculate points for football results prediction

    Still knocking on the "no IF/THEN" door...hehe...in G3:

    =((((E3 = B3) * (F3 = D3) * 2) + ((E3 > F3) * (B3 > D3)) + ((F3 >= E3) * (D3 >= B3))) * (COUNT(B3, D3:F3) = 4))
    Reply With QuoteRegister To Reply
  12. 08-15-2009, 11:46 AM #12
    dezmond
    dezmond is offline
    Registered User
    Join Date
    08-15-2009
    Location
    edinburgh
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula required to calculate points for football results prediction

    Hello mate I wanted the same thing and went about creating a spreadsheet its rather longwinded but it works im attaching it for you just enter the names on the point sheet in red area at the top along with their predictions in the grey area when you enter the score in the matches it will highlight blue for a correct score and white for a incorrect score but correct results. 3 points and 1 point respectively also included is a sheet for printing off and giving to people to put their predictions on the date for the matches auto updates hope this helps
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1