Last week I was helping a friover build a World Cup pool in Excel. It was fun debating the pool’s scoring rules. I also taught hyên a few Excel tricks along the way.
Bạn đang xem: World cup 2018 excel template
Excel Files
There are 2 files. Cleaning FIFA country list & World-Cup-Template-2018v2.
Data Cleanup (Flash Fill)
I found a country danh sách with FIFA rank. I used Flash Fill as it appeared khổng lồ be a simple pattern (remove 1st three letters). Further down ‘IRNIR Iran’ was converted khổng lồ ‘IR Iran’. That’s not right! Learn more from J-Walk!
Any Normal Person….
With such a small các mục and only 1 exception any normal person would manually correct the entry. I guess I’m not normal. I enjoy these puzzles. And what if the danh mục had been much longer? A formula can solve sầu it quicker than manually fixing entries.
Xem thêm: Nếu Tanker Đứng Cách Jinsoyun Quá 9M, Ả Sẽ Làm Gì? Tế Đàn Thiên Mệnh
What’s The Pattern?
Flash Fill made the first mistake. I made the second mistake.
Find The Last Upper Case LetterI thought that this would work. I obviously didn’t scan the data. “CODCongo DR” breaks the pattern.
Find The First Lower Case Letter & Go Bachồng One Space
This worked! In a nutshell, look for all lower case letters inside each text. We want the position of the 1st lower case letter. See my Excel file (above) for a full explanation of this array:
World Cup Pool Scoring System
The original idea was that everyone would select 2 teams from each group. However, everyone would piông chồng mostly the same teams. Where’s the fun in this? I recommended the following:
You get 1500 points khổng lồ select 2 teams from each groupPoints are based on FIFA rating for each countryThis way there’s more strategy in selecting teams. See my World Cup Template above sầu.
Data Validation Trick
Manually creating 8 different data validation lists seemed too tedious. Unlocking the column reference allows us to create only 1 data validation danh mục and then copy / paste it for all other groups