-1

I'm building a football results-related spreadsheet. I would like the 'outcome' cell (whether the result of a game was a win, draw or loss) to automatically populate depending on whether (i) the game was home or away and (ii) which team won.

Thus, I want the formula to do the following:

  1. Check cell A1 to see whether the game was home or away
  2. If the game was a HOME game, check if the number in cell B1 is greater than B2, if so, enter "W" in B3; but if B1 is lesser than B2, enter "L" and if B1=B2, enter "D"
  3. If the game was an AWAY game, check if the number in cell B1 is greater than B2, if so, enter "L" in B3; but if B1 is lesser than B2, enter "W" and if B1=B2, enter "D"

Is that at all possible?

  • The simplest version of a formula to do this is: =IF(A1="Home",IF(B1>B2,"W",IF(B1<B2,"L","D")),IF(B1>B2,"L",IF(B1<B2,"W","D"))). It assumes that A1 = "Away" if it does not = "Home" and assumes the scores are equal if they are not > or <. It would be better to expand the formula to catch those kind of errors. – Bandersnatch Sep 21 '17 at 12:56
  • The furthest I can get is: =IF(E11>F11,"W",IF(E11=F11,"D","L")), i.e. if number in left column is higher than right, put "W", but if it's equal, put "D" and otherwise put "L". The bit I can't work out how to tell it to treat it differently depending on whether it's home or away. – msrajjc2 Sep 22 '17 at 10:15
  • 1
    Thanks @Bandersnatch but this formula returns a 'parse error'. – msrajjc2 Sep 22 '17 at 10:21
1

Try:

=if(B1=B2,"D",if(or(and(B2>B1,A1="A"),and(B1>B2,A1="H")),"W","L"))

IF
OR
AND

  • 1
    Thanks for your help but this formula only seems to work when it is a home game, if the game is away it returns the wrong result. – msrajjc2 Sep 22 '17 at 10:19
  • 1
    No need to apologise at all - thanks for all your help - it works! I couldn't work out the syntax (I was putting the 'or' statement in after the first 'and' but as you've shown you have to kind of 'embed' it at the beginning - think I'm slowly getting it! Thanks so much – msrajjc2 Sep 22 '17 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.