# Google Sheets formula for three possible outcomes based on three cells

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
• Thanks @Bandersnatch but this formula returns a 'parse error'. – msrajjc2 Sep 22 '17 at 10:21

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