1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel formula help for my World Cup spreadsheet please

Discussion in 'Windows & Other Software' started by Participant, 11 Jun 2010.

  1. Participant

    Caporegime

    Joined: 13 May 2003

    Posts: 32,005

    Location: Warwickshire

    Hi all

    We've got a World Cup results prediction spreadsheet, whereby you get one point for guessing correct number of home goals, one for correct number of away goals, and one for the correct result.

    My prediction is in the first section, the actual results are in the second section, and the number of points scored I want to be automatically calculated when the game's result is entered.

    For the correct number of goals for each team, I've just done =IF(AND and it works fine. However, I can't work out a formula that will check whether the result was guessed correctly, whether win lose or draw.

    For example, if I had South Africa to draw 1-1 with Mexico and the score was actually 2-2, I'd get no points for number of goals but one point for guessing a draw. It's the last part I can't figure out.

    Thanks!

    Edit - to hopefully make it more obvious what I mean:

    [​IMG]
     
    Last edited: 11 Jun 2010
  2. Meeko

    Gangster

    Joined: 3 Apr 2003

    Posts: 440

    Location: Dunfermline

    Use the following formula mate :

    =IF(AND(B1-C1>0,E1-F1>0),1,IF(AND(B1-C1=0,E1-F1=0),1,IF(AND(B1-C1<0,E1-F1<0),1,0)))

    B1 = predicted home team goals
    C1 = predicted away team goals
    E1 = actual home team goals
    F1 = actual away team goals

    This is simply deducting the away goals from the home goals for both prediction and actual results

    A home win will always result in a +ve number, hence the first AND function saying >0
    A draw will always result in a 0, hence the second AND function saying =0
    An away win will always result in a -ve number, hence the last AND function saying <0
     
    Last edited: 11 Jun 2010
  3. Participant

    Caporegime

    Joined: 13 May 2003

    Posts: 32,005

    Location: Warwickshire

    Works perfectly, much appreciated.