0

I am doing a report on whether Expected Goals (xG) is an accurate predictor of the winner of a soccer game. I have every game from the 2021-2022 English Premier League Season organized into columns. I have each teams goals and xG listed in separate columns. I need to the sheet to choose the team that has a higher goals scored in one column and the team that had a higher xG in the next.

I would like for the sheet to automatically choose the winning team, and list the teams name in the winning team column for both actual goals and xG. Then seeing of these 2 winners match or not.

I have tried commands like this =INDEX(D2:J2,MATCH(LARGE(F2:H2,1),F2:H2,0)) but they end up only choosing the winning team if they are the home team.

I have included a link to the sheet below. If anyone could help it would be greatly appreciated.

https://docs.google.com/spreadsheets/d/1U3IpntjWkdqMt91E1PPNURITKKdvrbFOzbXbwzJzI1U/edit#gid=0

1 Answer 1

0

I haven't diagnosed your formula. Just offering another route I believe leads to the same goal, via IFs. K2 could contain:

=ARRAYFORMULA(IF(NOT(LEN(D2:D)),"",IF(F2:F=H2:H,"TIE",IF(F2:F>H2:H,D2:D,J2:J))))

Once again but in English:
ARRAYFORMULA once for each set of input data,
IF(NOT(LEN(D2:D)),"", if D is empty on a row, output a blank, otherwise:
IF(F2:F=H2:H,"TIE", if F and H match, output TIE, otherwise:
IF(F2:F>H2:H,D2:D,J2:J if F is greater than H, output the team in D, otherwise: J.

Then we can reflect the xG pairs in a similar fashion from L2:

=ARRAYFORMULA(IF(NOT(LEN(D2:D)),"",IF(E2:E=I2:I,"TIE",IF(E2:E>I2:I,D2:D,J2:J))))

And lastly compare them in M2:

=ARRAYFORMULA(IF(NOT(LEN(K2:K)),"",(IF(K2:K=L2:L,"Yes","No"))))

screenshot of the above formulas in action, each row showing the winner, expected winner, and whether those match

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.