Intro

I love football (or Soccer if that's how you roll). Each World Cup and Euro is a cause for intense joy, not just during the actual competition but before, trying to guess the corret outcomes with friends and coworkers.

That's why in 2010, since I knew a bit of Excel, I hacked this spreadsheet together that I and many other people have been using for quite some time now.

I've now updated the Excel for the FIFA World Cup 2018.

Download links for the impatient

You can download the spreadsheet from the following links or copy the link to your own Google Drive:

XLSX (Google Drive ) ODS (Google Drive)

Alternatively, you can use 4Shared to get it:

ODS (4shared) XLSX (4shared)

The Problem

We want to do the following:

• Be offline at all times (Complete privacy).
• No scripting or macros. Just plain old excel formulas that require no user privileges.
• Be able to enter the predictions of many participants.
• Be able to specify how many points a participant wins for guessing the outcome and how many for guessing the exact score.
• Have the rankings and standings be updated automatically and correctly.
• Handle the possibility of PKs.
• Give people the possibility to localize the spreadsheet if they're so inclined.
• Protect the spreadsheet cells that shouldn't be modified to breakage by user mistakes.

The Solution

Group Tab

Relevant functions:

The logic for the group tables is simple although verbose and mainly rests in the shoulders of two functions:

VLOOKUP

RANK

There's a group table to input values as they happen on the utmost left, then to the right of it, there's a "standings" table that will take advantage of VLOOKUP by looking for a specific position from 1 to 4 to fill all its values.

Where does it look the data from? Yet another table in the right of it which has fixed positions for each team and computes values like points, goals in favour and goals against. It also ranks each of those columns to see which team leads for each respective category and finally, the magic happens in the coefficient column. It just multiplies each column by powers of ten creating a sorting hierarchy that can be used by the previously mentioned VLOOKUP.

Give people the possibility to localize the spreadsheet if they're so inclined.

To achieve this there's two columns in AA and AB where you can find translations for two languages. All the texts you'll see in the "menus" will compare to the B1 cell to see which column/language to use.

Localizing this to a different language than English/Spanish is easy. Just replace the appropriate column and the B1 cell.

Stage Tab

This one is pretty simple and just consists of a judicious use of IF commands that takes into accounts ties and the light blue cells for PKs.

Rankings Tab

This is just a nice view for the real logic that is performed in the database tab.

Database Tab

Here be dragons.

Stadiums Tab

Some eye-candy.

Readme Tab

Quick instructions and contact info.

---

That's it!

Was this helpful? Do you have any suggestions? All comments are welcome!