Help making World Cup Spreadsheet perfect

[luis75]

Hi guys,

I am having a little trouble putting together a useful World Cup 2014 worksheet. My goal is this: To create a worksheet where all I have to do is input the scores for each game and the points are added up automatically, and the fixtures for the playoff round are made automatically. Once the fixtures for the first playoff games are made, then all I would have to do is put in the scores and then the winner will automatically move on and finally I will be able to determine what the final game will be.

So far, everything works. I have all the matches and when I put in the scores, I have the points calculated for each group. I have the team with the highest point total in its group appear where I want it to and the team with the second highest total in its group appear where I want it to.

Here is my problem.

For those unfamiliar with the world cup, the teams that move on to the next round are (as I stated before) the ones with the highest and second highest point totals in their group. That would be great if there wasn't a giant problem: It all falls apart when two teams have the same amount of points.

The way the two top teams of each group are determined if they have the same amount of points is then goal differential. So lets say Ecuador and France both have 4 points and are fighting for the second slot in the group. Ecuador scored 6 goals but got 3 goals scored on them (giving them a goal differential of +3) and France scored 4 and had 3 goals scored on them (giving them a goal differential of +1) then Ecuador should be the team that moves on.

For the life of me, I can't figure out how to incorporate that into my worksheet. Again, I will be inputing the goals scored so there should be a way to calculate goal differential but then adding that criteria to determine which of two teams with equal points can move on is way over my understanding of excel haha.

Here is my file if anyone wants to download it and have a look:

https://www.dropbox.com/s/dwy67dp7petd996/WORLDCUP2014.xlsx

Thanks for the help!

 

[ace19852]

Hi this may not be as easy as you hoped maybe someone else will come up with a better solution.

My solution was this. I inserted 3 columns K, L and M and looked at only one Group B as an example.

in cell J12 "=(D12+D14+F16)-(F12+F14+D16)" ... K12 "=RANK(I12,$I$12:$I$15,0)" ... L12 "=IF(K12<>1,"",1/J12)"
in cell J13 "=(F12+F15+D17)-(D12+D15+F17)" ... K13 "=RANK(I13,$I$12:$I$15,0)" ... L13 "=IF(K13<>1,"",1/J13)"
in cell J14 "=(D13+F14+F17)-(F13+D14+D17)" ... K14 "=RANK(I14,$I$12:$I$15,0)" ... L13 "=IF(K14<>1,"",1/J14)"
in cell J15 "=(F13+D15+D16)-(D13+F15+F16)" ... K15 "=RANK(I15,$I$12:$I$15,0)" ... L13 "=IF(K15<>1,"",1/J15)"

and in cell O14 put

=INDEX($H$12:$H$15,MATCH(LARGE($L$12:$L$15,2),$L$12:$L$15,0))

So this needs to be repeated for all groups

I hope this example is something you can work with.

Hide columns if you don't want them to be seen.

Just 1 thing what happens if after goal diff is calculate it's the same ??

Anyway have a play

Cheers

 

[MARK858]

Just 1 thing what happens if after goal diff is calculate it's the same ??

Page 52

http://www.uefa.com/MultimediaFiles/Download/competitions/Regulations/01/87/54/21/1875421_DOWNLOAD.pdf

 

[PaddyD]

Parry, an MVP who hasn't been around for a while, put a great file together for the last two world cups. 2010 version is here:

https://sites.google.com/site/paramorefifawc/

...which you might be able to play with / learn from.

 

[RickXL]

Welcome to the forum.

Excellent spreadsheet!

I must warn you that I know nothing about football but I hope I have understood your requirements.

I propose using an extra "helper" column. This need not be on show, you could hide it. The contents of the extra column will be a combination of the the points in column I and the calculated goal differential.

I used SUMIF to calculate the differential. I used the team name in column H as the criterion and summed the points for in D and F and the points against in D and F are subtracted. I hope that is right. The combined formula is:

=SUMIF(C:C,H6,D:D)+SUMIF(E:E,H6,F:F)-SUMIF(C:C,H6,F:F)-SUMIF(E:E,H6,D:D)

Assuming you are not expecting any tied teams to have goal differentials exceeding 99(!) then you should be able to combine the points and differentials by multiplying the points by 100 and adding the differentials. Like this:

=(100*I6)+SUMIF(C:C,H6,D:D)+SUMIF(E:E,H6,F:F)-SUMIF(C:C,H6,F:F)-SUMIF(E:E,H6,D:D)

I think you should then be able to use that new column in place of column I in your LARGE functions in column M, for example.

 

[ace19852]

I will have a look at the 2010 world cup file mentioned above to see if it's easy to amend

https://sites.google.com/site/paramorefifawc/

However I have amended your file see link

Download WORLDCUP2014AMENDED.xlsx from Sendspace.com - send big files the easy way

I used RickXL suggestion above in column J and also added a rank column K

and then gave range names to the groups e.g. GROUPA, DIFGROUPA, RANKGROUPA through to H

in the playoff stages I used for the left hand side qualifier

=INDEX(INDIRECT("GROUP"&RIGHT(M6,1)),MATCH(LARGE(INDIRECT("DIFGROUP"&RIGHT(M6,1)),1),INDIRECT("DIFGROUP"&RIGHT(M6,1)),0))

and for the right had side used to get the second best team in the group

=INDEX(INDIRECT("GROUP"&RIGHT(AC6,1)),MATCH(2,INDIRECT("RANKGROUP"&RIGHT(AC6,1)),FALSE),1)

However this doesn't not cover all situations for ties. I believe another level i.e. number of goal scored is needed. At the moment it only copes with goal difference.

Have a look at the file and see what you think I loaded some dumby scores to check it out

Cheers

 

[ace19852]

Hi I found Parry's sheet via the link above and I learned quite alot from it and decided to try and replicate it to some extent but no sounds or Macros. But did it for the boys at work. I also noticed the Fifa site had some good templates on it which is worth a look.

Anyway here's what I did within the time constraints

Download WC2010 xl2007 v1 CRW amended.xlsm from Sendspace.com - send big files the easy way

Cheers

PS was a rush so hope no mistakes were made but you never know and all you have to to is fill in the yellow cells with the scores as you go