exceltemplatespredictioneuro

Excel sports template rank table

I am currently working on a sports template prediction for the upcoming Euro 2020. I haven't gotten that far yet but below is an example of a group. So the "Tecken" column says which team won the game, if there is a 1 there the home team won, if there is a X there it was a draw and if it's a 2 there the away team won. What I would like to do is have a formula in the points column in the second table that checks which is the home team playing and if there is a 1 under the "tecken" column add 3 points to the points table. And if there is a X I would like to add 1 points for that team. I also need to do this for the away teams and add points (if there is a 2 in the "tecken" column) to the points table. Sorry for the bad explanation, is that clearer?

So for spain it would check the first game and see that spain is not playing. It would check the second game and see that spain is the home team and that there is a 1 in the "tecken" column and add 3 points to the points table and so on.

(GF is goals scored by each country and GA is goals scored against each country and GD is the goal difference)

Here is one group example

Solution

  • You can use a formula with CountIFs. Conceptually:

    Something like this. Adjust the ranges to your sheet.

    =(Countifs(C:C,K2,I:I,1)*3)+Countifs(C:C,K2,I:I,"X")+(Countifs(E:E,K2,I:I,2)*3)