Ladies and gentlemen, I have just completed possibly the most monumental bit of football statistical analysis I’ve ever done.
Even bigger than the research on the potential for there being nine teams 2-0.
This research has taken me several hours, scouring AFL tables, and making a heck of an Excel spreadsheet.
It is the Streakometer.
To elaborate on what’s going on with this, I will show you a screenshot, before explaining the things in it.
As you can see, we have a list of each of the AFL clubs, today’s date, a series of columns from 2 to 26, and a whole host of coloured cells. Most of them are green, some are slightly different shades, and there is a reasonable amount of grey.
In a sentence to explain this, it shows how long it has been since the last time a club had a streak of x number of games undefeated. Upon consideration, that didn’t help at all.
Look at Fremantle, for an example. Suppose we wanted to know the last time Fremantle had won nine games in a row. To do this, we would find Fremantle on the left, and then look until we were at the column labelled 9. Here, we get 343.
343 is the number of days since Fremantle last had a nine-game undefeated streak. As for how this is calculated, I used the Excel formula
Cell A22 has today’s date (as I write this, the 7th of May, 2016), and cell AI7 has the most recent date that Fremantle won a ninth game in a row (the 30th of May, 2015).
I’ve done this for every single club.
To those still curious;
- The grey cells are ones that a club has never achieved. For example, Gold Coast winning 17 in a row is greyed out, because Gold Coast never has. In the unlikely event that they win their next 17, I’ll have to fill that in.
- The cells are using conditional formatting. The greener a cell is, the more recent the event happened.
- The cells in bold are the most recent occurrences by any team for that streak.
And below today’s date, I have the ‘Current Streaks’ section, which will enable me (and you) to keep note of all the games we have to watch.
A few of the things we can note are:
- Essendon and St Kilda are the only teams that haven’t won two in a row in the past year.
- Out of the Victorian clubs, the Western Bulldogs have the lowest all-time streak.
- Hawthorn, Sydney, Fremantle, Gold Coast, and GWS have their all-time records in the past three years.
- If North Melbourne wins tomorrow (after this is published), it will be their longest winning streak since 1999.
And with that, I send the spreadsheet over to you. Feel free to play around with it, update it yourself, and send me what you notice over Twitter. I’ll continue to update this until I get bored with it.