How can you collate sports fixtures or results into a league table in Excel?
Download file link:
Video 1 - INTRODUCTION
Video 2 - convert scores to a result using if
Video 3 - collate wins and losses using countif 1
Video 4 - collate wins and losses using countif 2
Video 5 - use sumif to find goal difference
Video 6 - use match and offset to create the league table
Sport enthusiasts might wish to create a league table from a set of results in Excel, but it can be difficult to know how to start. Without good pivot table or VBA programming skills, it is a complex task that cannot be completed in one fell swoop. It necessitates some clear, logical thinking about the required steps, and the creation of a well-structured Excel file.
This invites us to reflect on what a well-structured Excel file looks like. We propose a structure comprising three elements - a backend, calculations and frontend - and apply it in the video series. This is a good general structure to apply to your next Excel-based task!
Along the way, we apply Excel formulae that are essential in spreadsheet modelling including if, sumif, offset, match and vlookup.
As a starting point, Chris takes the results data from the 2015-16 Premier League season and works through the steps towards a league table. In the final video in the series, Chris tests the model created against the actual Premier League table. Will it be accurate?
For regular spreadsheet hints and tips and more on the #ExcelRevolution: