![]() ![]() ![]() If tied on points, use overall goal difference as the tie breaker.Points: 3 points for a win, 1 point for a draw, 0 points for a loss.The next challenge for myself will be to build the logic using PowerPivot and DAX.Īt a high level, the logic that I built is as follows: BTW - I know there are multiple ways of building this in Excel, and would love to see other approaches. The fun part was building the logic for Points, Goals and tie breakers exclusively using Get & Transform. The result was a clean table with just the scores for each game (see below).įirst set of transformations in the Query Editor I cleaned the data by applying several transformations in the Query Editor, including removing columns and errors, splitting columns, joining tables, adding conditional columns, etc. Note the ribbon might look a little different depending on which version of Excel you have. Of course, this data is super messy, so the first thing I did was to bring it into “Get & Transform” using the “From Table/Range” button in the “Data Tab”. Since we are only 2 games away from the end, I don’t care too much about having a live connection to the website. However, if we had more games to go and I wanted the scores to update, I could have used the “From Web” connector. Since I’m not anticipating the historical scores changing, I simply copied the results from the site into the “Source Data” tab in Excel. I sourced some raw data from Wikipedia with the scores from all the previous games. If you have Excel 2010 or Excel 2013, you can also take advantage of these capabilities by downloading the latest Power Query for Excel add-in. Note that Get & Transform is available natively as part of Excel 2016 and Office 365 subscriptions. I’m providing a link to the Excel file at the end of the post, so you can predict your own results. If the standings hold, Peru will qualify. Against all odds, Peru is in good shape this time around, and currently sits in 4 th place. Currently, there are 2 games left to be played in South America, on October 5 th and 10 th. My predictions on how the last 2 decisive games will play out, and the results from the model. Since I LOVE Get & Transform (a new set of powerful Excel 2016 features which provides easy data gathering and shaping capabilities), I thought that this time around I would build the logic exclusively using this new tool. That is why every 4 years, my ritual, as we approach the end of the tournament, is to build a model in Excel to predict the score of the final few games and play around with different scenarios. “We’ll make it only if Argentina ties with Chile, AND Colombia beats Brazil by 3 goals, AND…” Everyone becomes a math expert and creates elaborate result predictions and scenarios. In Peru, we have a saying: “Pray to the calculator gods” because usually whether we qualify depends on the results of other teams. With such a difficult group, who qualifies always comes down to the last few games. Unfortunately for Peru, per DeadSpin, this year’s South American qualifying group is deemed the toughest in the world. However, though potentially delusional, I know this is our year. Peru’s last appearance in the finals was 35 years ago, and Slate Magazine describes my support for Peru as " an act of fatalism". I’ve included the best below.As a diehard fan of the Peruvian Soccer (Fútbol) Team, I’m obsessed with the South American Soccer Qualifying Tournament. These will usually be purchasable downloads but there are some good free options as well. Some fillable Football Squares can be quite involved and even do a lot of the calculations for you (the maths!). With the Google Sheets option, everything is online so you’ll just need to make a copy of the Football Squares sheet and share with everyone. After the info on the Football Squares is entered, you can save the file and then send via email or share online. With fillable PDF and Excel spreadsheet formats, you can easily fill in the squares with participant names, teams, and numbers. Can be easier to enter players and do calculations.Great for groups that aren’t all together physically.With a fillable Football Squares board, advantages include: With so much of what we do these days being online, a digital version may be a better format for the big game. Before we go any further, you may want a refresher on how to play Football Squares.Īt an in-person football game or at the office, you’ll usually play Football Squares on a physical paper where everyone sign up for squares and then passes around, or at a party with a poster version. Fillable PDF, Excel Spreadsheet and Google Spreadsheet are common template formats for Football Squares. If you’re looking for an easy way to play Football Squares online with friends, you’ll want a digital template file that you can share with everyone. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |