Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Iam going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.

Image for post

Introduction

I

am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.

In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. Even though the update was rolled out to my copy towards the start of the year, there were still swathes of users who were kept waiting.

Since dynamic arrays were introduced in Excel, array formulas no long require you to press Ctrl + Shift + Return every time you edit a cell. This was an annoying practice that made many users, including myself, reluctant to use arrays. They just didn’t feel like a native and integrated part of Excel. Now you can use an array formula like any other — without that additional step.

If you want to find out more about the new functions, I recommend you visit Microsoft’s help page for each one: XLOOKUPFILTERUNIQUESORTBYSORTSEQUENCERANDARRAY.


Before we start

Download the workbook from here: https://bit.ly/39mlqkp.

Quick caveat: if you have an older version of Excel, you will find some of the examples do not work because of compatibility issues. This is unavoidable unless you purchase a Microsoft 365 subscription. Personally, I would recommend you do so.

Steps taken

Firstly, a dataset is required containing a list of all the matches played and their respective results. I have used English Premier League data from the 2019/20 season for this example. To conserve space elsewhere, the matches are stored in a separate worksheet called Data — with the table itself named DataTable.

Image for post

2019/20 Premier League Dataset

You’ll notice there’s a calculated column on the end called Result. This formula looks at the home_goal and away_goal fields for each match played and determines whether the outcome was a home win (H), draw (D) or away win (A).

There are three sections: Part APart B and Part C. Each contains multiple league tables that output identical values, but the method used differs.

Any kind of system that involves ranking data is typically going to require an unordered and ordered table. The former houses the mathematical calculations and determines the ranking of each row, whilst the latter references it to output the data in the correct order. Part A and Part B are based off this principle. Part C, however, contains two variants that are not dependent on an additional table.

The tables in the workbook use these headers:

  • POS (position)
  • TEAM
  • P (matches played)
  • W (matches won)
  • D (matches drawn)
  • L (matches lost)
  • F (goals for)
  • A (goals against)
  • GD (goal difference)
  • PTS (points)
  • RANK* (table position when points are sorted in descending order)

*Table A2 only

Part A

The approaches here are all based on official Excel tables. The way to tell if what looks like a table is indeed a table— is to check if it has a small blue triangle in the bottom-right, or to click on it and the Table Design tab will appear in the ribbon.

We start off by creating Table A1, *which is unordered and forms the base for *Table A2, Table A3 and Table A4 *to work off. The *PWDL columns use COUNTIFS formulas to count the number of matches a team has played, won, drawn and lost respectively. It’s important to note that a single COUNTIFS formula only allows for AND conditions. That means all criteria must be met for a successful count. As we have home and away matches to consider, we need to use two COUNTIFS statements in the same cell to add the counts together. The same concept applies to the SUMIFS function, which has been used for the columns that involve addition: F and A.

excel football premier-league soccer dynamic-array

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Why the Premiere League is not the Best League

As a soccer fan you can hear it everywhere “Home of the best league: The premier league”, “The best league in the world is the premier league” … . Many people, but also professionals such as journalists and commentators regard the premier league as the best league in the world.

Fantasy Football App Development I Fantasy Football Software Development

Fantasy Football App Development- Expert in providing world class Fantasy football software development, Fantasy Football website development and app development services.

How to Build a Dynamic Array By Using a Static Array in Java

We all know about the basic data structure, which is Array pretty well. And in java they are static. It means we have to allocate memory for the array ahead of time.

Excel VBA Tutorial for Beginners 58 - 1D Arrays in Excel VBA

In this MS EXCEL VBA video, we are going to see some time related functions in MS Excel. While using these functions, we can easily customize time in MS Exce...

Leading Football Betting App Development Company

Are you struggling to design a robust football betting app? Mobiweb Technologies guaranteed you to deliver a secure football betting app with some advanced features. Our mobile app includes a variety , 6098913256