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 ổ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 and World-Cup-Template-2018v2.
Data Cleanup (Flash Fill)
I found a country danh mục with FIFA rank. I used Flash Fill as it appeared lớn be a simple pattern (remove sầu 1st three letters). Further down ‘IRNIR Iran’ was converted to lớn ‘IR Iran’. That’s not right! Learn more from J-Walk!
Any Normal Person….
With such a small danh mục & 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 các mục had been much longer? A formula can solve it quicker than manually fixing entries.
Xem thêm: Hướng Dẫn Sửa Lỗi Ghost Xong Không Vào Được Win, Không Bootwin Sau Khi Ghost
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 và 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 lớn 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.
Data Validation Trick
Manually creating 8 different data validation lists seemed too tedious. Unlocking the column reference allows us khổng lồ create only 1 data validation list and then copy / paste it for all other groups
