2014 World Cup Spreadsheet

Discussion in 'World Cup 2014: General' started by benztown, Dec 7, 2013.

  1. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    Edit made on behalf of benztown is in red font. Please read this first since it contains the most up-to-date information:

    "Thanks to the help of the BigSoccer community, I did iron out some issues with the original sheet. The current version can be found by following this link:

    http://www.bigsoccer.com/community/threads/2014-world-cup-spreadsheet.1997715/page-2#post-30125142


    The most important update is a correction of the tie-breakers if two or more teams end up with equal points. Additionally, some minor improvements were included."

    The text of the original message is here:



    Once again, I built a WC Excel spreadsheet so that you can play through all possibilities before the World Cup even begins.

    You can download it here from a file hosted on the web:
    http://goo.gl/tL2uhf

    Or you can download the ZIP file I attached to this thread (BigSoccer doesn't take Excel files, so you'd have to unzip it first).

    The beauty of this spreadsheet is that it doesn't use any script language or anything like that. It's all done purely with Excel formulas (which can be very tedious to implement, I tell you) which has the advantage that you don't have to press any buttons or anything. As soon as you enter the score, everything is updated instantly.

    Should you find any errors anywhere, please feel free to tell me and I will correct them ASAP. I did simply adapt my sheet from 2010, so in theory the mathematics should work just fine (unless there are new rules for tie-breakers?). Of course I could have made some errors when I adapted the sheet as the setup of the tournament is slightly different this time around, which did cause me more headaches than I anticipated (that's the disadvantage of not using code underneath). However, I did a couple of test runs and didn't encounter any problems, but I don't have the time to test for any eventuality.

    Again, it should be fine, but in the unlikely event that it isn't, please tell me.

    Attached Files:

    Last edited by a moderator: May 13, 2014


  2. Stefan79

    Stefan79 Member

    Joined:
    Nov 20, 2013
    Location:
    Amsterdam
    Club:
    AFC Ajax
    Country:
    Netherlands
    Very good. But how do you determine the winner of a group if points, goal difference and so on is the same?
  3. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    I've used all the regular tie breakers: Goal difference, goals scored, head to head result. If teams still end up equal, then AFAIK FIFA uses lots. Of course I couldn't implement that. I mean I could generate random numbers, but they would change every time you type in anything, so they'd be impractical. Instead, I simply go by the position the teams were drawn into their group. So if a team that was drawn into D2 ends up equal with the one in D3, the one in D2 proceeds. Not ideal I admit, but then it's rather unlikely that this would ever happen. And even if it does and you type in the results as they happen, you still have a 50% chance of having the right team get through. On the off-chance that it does happen and the team that was drawn lower into the group gets the lucky draw, you'd have to hack the scores I guess.
    Stefan79 repped this.
  4. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    To clarify:
    In my sheet, the first tie-breaker is the head-to-head result, then comes goal difference, then goals scored.

    Of course I also implemented cases like say Team A beats Team B, Team B beats Team C and Team C beats Team A. If those three teams end up with the same amount of points, head-to-head results aren't factored in for obvious reasons, instead we'd go straight to goal difference.
    Stefan79 repped this.


  5. schnix

    schnix Member+

    Joined:
    Jan 2, 2012
    Location:
    Brooklyn, NYC
    Club:
    AS Roma
    Country:
    Germany
    danke @benztown, your spreadsheet looks mine look like a vagina
  6. korfan

    korfan Member

    Joined:
    Aug 24, 2013
    Club:
    Manchester United FC
    Country:
    Korea Republic
    Found a glitch. In Group A, for fun, I gave everyone 2-2 scorelines except Croatia-Mexico, where I put it at 1-1. Yet Croatia has a +1 GD with 6 GS instead of 0 GD and 5 GS.

    :D

    EDIT: Only seems to happen in A
    benztown repped this.
  7. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    Thanks. I already found the bug. When adapting the sheet I had to change the sums, apparently I somehow missed to adapt the ones for Croatia's goals scored, so it added up the wrong numbers...sorry, I guess I should have found this before...

    I attached the updated version 1.1 to this post...

    Attached Files:

  8. schnix

    schnix Member+

    Joined:
    Jan 2, 2012
    Location:
    Brooklyn, NYC
    Club:
    AS Roma
    Country:
    Germany
    can you have a separate column for PK's? maybe put it in column U/V
  9. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    I originally decided against it in order to keep the sheet as clean as possible.
    I don't think I feel like putting in the time necessary to update the formulas and the layout to be honest.

    If you want to take a stab at it however, then send me a PM and I'll give you the password. I don't want to post it publicly because I really put in a lot of work.
  10. SJJ

    SJJ Member

    Joined:
    Sep 20, 1999
    Location:
    Royal Oak, MI, USA
    Club:
    Michigan Bucks
    Country:
    United States
    The official regulations have:

    41 Group stage
    5. The ranking of each team in each group shall be determined as follows:
    a) greatest number of points obtained in all group matches;
    b) goal difference in all group matches;
    c) greatest number of goals scored in all group matches.
    If two or more teams are equal on the basis of the above three criteria, their rankings shall be determined as follows:
    d) greatest number of points obtained in the group matches between the teams concerned;
    e) goal difference resulting from the group matches between the teams concerned;
    f) greater number of goals scored in all group matches between the teams concerned;
    g) drawing of lots by the FIFA Organising Committee.


    Also, I edited group A matches, randomly entering results:
    1:2
    2:1
    1:1
    3:2
    1:4
    3:5
    which led to the Round-16 getting Mexico entered in match 49, and Greece (huh?) entered in match 52.
  11. Jorge Villamarin

    Jorge Villamarin New Member

    Joined:
    Feb 5, 2014
    Club:
    Millonarios Bogota
    Is it possible you share the password, I would like to make some changes for personal usage.
    Thank you!
  12. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart

    Crap, so they did change it...I might have implemented this with my last EURO-Spreadsheet, if I did, I will certainly update the current sheet within the next couple of days. If I didn't, it might take a while longer as I currently don't have the spare time to delve into this and since I don't want to use Visual Basic for philosophical reasons, it's definitely quite a piece of work...
    schnix repped this.
  13. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    I don't want to post it publicly. But I've sent you a PM. As long as it's for your private use I have no problem with it.
  14. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    Ok, so unfortunately, I don't have my old EURO sheet anymore. But I have gotten to work on the formulas and I think I've cracked it. It has become very convoluted and complex and I have to make many small and major changes to individual cells everywhere. But anyway, It works with Group A for now, so the hard part of devising the formulas is over, now it's a matter of adapting them for each group...It's 120 new formulas for each group...most of which I can just copy and paste, but as the order of games is different in most groups, I do have to adapt 24 of those for every group which is very tedious, so it could take a little while longer...
    schnix repped this.
  15. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    The most amazing thing just happened: I realized that the order of the schedule is really the same for every group, so I can just copy the formulas and don't have to adapt them to every individual group...it was different 4 years ago, so I also had to make changes to all the formulas when creating this years spreadsheet which is probably why I thought that I had to do the same again.

    Anyway, version 2.0 will be here any moment...:)
  16. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    So, here it is in all its glory:
    This is the updated version with the correct tie-breakers implemented!

    Feel free to download the attached file. I did check out various scenarios and the results were always correct. But since there are 120 new formulas, it's always possible that a tiny error has crept through unnoticed, so should you run across a bug, please tell me about it so that I can correct it.

    Attached Files:

    TigersOfAsia, Iranian Monitor and schnix repped this.
  17. Iranian Monitor

    Iranian Monitor Member+

    Joined:
    Aug 18, 2004
    Location:
    Tehran Iran
    Thanks for the spreadsheet. I entered my predictions without giving them extra thought and I ended up with standigns that differed in some cases from what I had anticipated.
  18. El_Bulla

    El_Bulla Member

    Joined:
    Jan 21, 2007
    What standings changed specifically? As in what teams went through that you didnt expect
  19. Iranian Monitor

    Iranian Monitor Member+

    Joined:
    Aug 18, 2004
    Location:
    Tehran Iran
    It was mostly the placement of teams, not who went through. Specifically, in the group of death, Chile ended up on top of the group whereas I always thought they would advance ahead of Holland but behind Spain. I also found Russia finish first in their group ahead of Belgium, whereas I had earlier picked them to advance as the second place team in their group. England also did better winning their group and finishing as a quarterfinalist. There were some other changes in the points and standings, but they were mostly peripheral.

    Incidentally, as I explained in another thread, with Chile advancing as the top team in its group, that also changed my predictions as far as the semifinalist in 2014. Specifically, whereas I had predicted previously Argentina to win, Brazil to finish runner up, Germany to finish 3rd and Spain 4th (all top 4 making the semifinal with no surprises teams among them), now I see Chile finish 4th instead.
    El_Bulla repped this.
  20. BorisG

    BorisG Member

    Joined:
    Sep 30, 2009
    Location:
    Namibia
    Club:
    FC N├╝rnberg
    Country:
    Germany
    Thanks Dude
  21. Roger Ramjet

    Roger Ramjet New Member

    Joined:
    Feb 26, 2014
    Club:
    Rangers
    thank you very much for this benztown - could you also send me the password? I'd really love to use this as a basis for making a wee game for the people in my office this summer
  22. Lusankya

    Lusankya Moderator Staff Member

    Joined:
    Nov 14, 2007
    Location:
    Germany
    Country:
    Germany
    It's always like this for the World Cup.
  23. benztown

    benztown Member+

    Joined:
    Jun 24, 2005
    Club:
    VfB Stuttgart
    Are you sure? I seem to remember that head-to-head result used to be the first tie breaker ever since 1998 or 2002...also, I might be imagining things, but I really thought that I looked this up before the 2010 WC in order to correctly implement it in my spreadsheet then...but maybe I'm simply confused...
  24. Lusankya

    Lusankya Moderator Staff Member

    Joined:
    Nov 14, 2007
    Location:
    Germany
    Country:
    Germany
    Well, most (all?) of the continental tournaments use H2H before GD now, but the FIFA World Cup always used GD first. Some WC qualifying rounds were different, though, and used H2H first (I think 2006). Either FIFA were just experimentating during that cycle or the continental confederations were in charge of the qualifying systems.
  25. duquesito

    duquesito New Member

    Joined:
    Mar 8, 2014
    Club:
    Real Racing Club Santander
    Hi Benztown,

    Thank you very much for your work. I've been testing it a bit and it looks good for me. I'm doing a spreadsheet of my own but the part where I have to implement FIFA rules from d) to f) is very difficult for me.

    Again, thank you very much

Share This Page