Is this a FIFA worldcup of late goals? Lets ask Excel

Share

Facebook
Twitter
LinkedIn

Just like millions of viewers around the world, I too have been spending hours watching FIFA world cup football matches on TV. I don’t like spending hours watching TV. But when its FIFA world cup time (which is once every 4 years), I am glued to the idiot box. Blame it on PaWaRa, my school teacher in 8th grade who instilled this passion.

So while watching the match day before yesterday (it was Holland vs. Chile), the commentator said, “This has been a world cup of late goals” as both teams maintained 0-0 until 77 minute mark when Leroy Fer scored a goal for Holland.

That got me thinking,

Is this really a world cup of late goals?

But I quickly brushed away the thought to focus on the match.

Later yesterday, I went looking and downloaded all the goal data for 2006, 2010 & 2014 FIFA world cup matches (2014 data for first 36 matches).

Lets examine the hypothesis “2014 has been a world cup of late goals”.

Attempt 1: Distribution of goals on 90 minute timeline

There have been 147 goals in 2006, 145 goals in 2010 and 117 goals in 2014 (as of 24th June, 2014). Out of all these goals, only 5 goals were scored after the 90 minute mark. So I ignored these 5 goals for our analysis.

Also, I assumed that any goals scored in injury time are part of the 45th minute or 90th minute mark (for simplicity).

One more: I have included data only up to 23rd of June, 2014 – so only first 108 goals of this edition are considered. This reflects accurately the moment commentator made that remark.

Lets see the chart.

Distribution of goals in fifa worldcup (2006, 2010 & 2014) by time - All goals

Each dot depicts a goal. The dots are filled with semi-transparent color, so we can see the density of goals at each point of the 90 minute timeline.

As you can see, there is no clear pattern of late goals in 2014.

While we could see higher density of dots in first half of 2006 & 2010 editions, that can be attributed to having full data vs. partial data (for 2014).

Attempt 2: % of goals scored in each 15 minute block

May be if we look at % of goals scored in each 15 minute block, we can conclude something.

Distribution of goals in FIFA worldcup - All goals + 15 minute blocks

This gives an indication that 2014 world cup indeed has slow first half. But then you also see conflicting proof with more goals scored in last 30 minutes in 2006 & 2010 editions.

Attempt 3: What if we consider only first 100 goals in each world cup

Lets remove some noise. The commentator said this has been a world cup of late goals. If we consider only first 100 goals (ie first 30 odd matches) in each world cup may be we can see how 2014 fares compared to 2010 & 2006 editions.

Goal distribution - FIFA worldcup - first 100 goals in 2006, 2010 & 2014 editions

Here too the chart does not reveal much. If anything, we can conclude that 2006 has clear pattern of high number of goals in first & last 30 mins.

While 2014 has high density in the last 30 mins, it has good distribution throughout the 90 minutes.

Attempt 4: Lets consider only the first goal of each match

I guess the impression of slowness is created if you have to wait a lot of time to see the first goal in any match. After that usually things pick-up.

So what if we consider only the first goal times in each match.

This is what we get.

Goal distribution - only first goal in each match - FIFA worldcup - 2006, 2010 & 2014.

Now this is clear. You can see that 2014 has high density in first half. Remember, for 2014 only 36 matches data is considered where as 2010 & 2006 have 64 matches data.

But we can also see the high density of goals in first half for 2006.

If you look at the average wait time for first goal, 2006 is the least with 30 mins and 2014 is in second place.

So if any, we could say 2010 was the world cup of late goals.

Attempt 5: Cumulative % of goals by minute

If a particular world cup has many late goals, then it will show thru when we plot cumulative goal distribution (as a %).

Here is what we get.

Cumulative distribution of goals in FIFA worldcup - 2006, 2010 & 2014 editions

From this you can see that 2014 line lags behind 2006 & 2010 for first 60 minutes, before climbing to top place.

This does indicate that 2014 has a lot of late goals.

But the difference is negligible, so we cannot really say much.

What do you think?

I do feel that some of the matches are slow to watch. But this is purely because I have been looking forward to the world cup and could not wait for the action.

What do you think? Do you think this has been a world cup of late goals?

Also, tell me what you think about this analysis? Wow or meh?

About the data

Thanks to Soccer Worldcups & Wikipedia from where I obtained this data.

More like this

If you want to dig a few a more charts and see how they can help you analyze data, check out:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

15 Responses to “A Gantt Chart Alternative – Gantt Box Chart”

  1. Kenjin says:

    That's a great idea.
    Maybe the planned End Date should be highlight more.
    I don't know how it would look like (nor how to do it yet), but what if instead of finishing the bold line to the best case End Date, it finishes to the realistic End Date?

  2. ross says:

    The idea is ok, I think other project management tools have this, already? Maybe not.

    Gantt charts in my view are about the signal most unless thing in the world, theres no way you can look at one thats more that a little complex and understand what it's telling you. I'm going to write a diatribe on project management at some point, its one of my pet areas I think!! 😉

    The issue I have with this chart Chandoo, is that Tasks need to be linked to each other, so they should inherit the uncertainty, which would mean the as you moved down chart the lines would be miles apart for later tasks, and you might have to add lots of lines for subsequent tasks to cover the various outcome of it's parents.

    Having said that, for the high level board summary, it's a nice way to go, it it appeals to the management 😉

    thanks Chandoo, great post.

    Ross

  3. Cyril Z. says:

    Whoooa !!! That's a very clever idea Chandoo. I really love it.
    I think i'll update my gantt project sheet with that idea soon (remember my template ?)
    @ross : you can link start date to the end date of the previous task in your data. The only problem I still se is to which end date (real ? planned ? best ?) in order to have average amount of information.

    If best end date, you'll tend to increase uncertainty at the end of chain, although if you link to real end date, uncertainty will be decreased too much, leading in both cases to wrong management direction.

    Maybe planned till the task is finished then real will do the job ?

  4. Vijesh says:

    Hey chandoo, this looks good and this would definite add value in production planning / scheduling. Uncertainity in finishing a task is very high in production scheduling and this could give an insight or a bird eye view of possible shipments we can have....

  5. PK says:

    I've always been frustrated by the limitations of gantt charts. Will definitely use this, I've always struggled with how to succinctly communicate the uncertainty of certain tasks without confusing stakeholders.

  6. Andy says:

    I like this, I think it's a very effective way of showing how a timeline can change and which parts of a project need close attention.
    @Cyril / @Ross: I would intially link the the start date to the planned end date of the previous task, with the chart updating when a task has been completed to reflect the true end date.

    Or what about giving a drop-down selection box to allow the user to see the chart based on planned/best-case/worst-case end dates?

  7. Eric says:

    Like the idea. Have found that Excel is more flexible than MS Project for graphical solutions. The "Best Case"\"Worst Case" metrics are theoretically appealing but once the project and\or phase commences their reliability diminishes. A chart like the above that showed Planned Start, Planned End, Replan End Start, Replan End Date, Number of Replans the Start and End Dates, and Actual would provide an active, actionable view of each task\phase. It would also highlight the areas which are riskiest.

  8. Bob says:

    It is always amazing how flexible excel can be.

    My question is how would the chart show a scenario where the date moved up? If a task is dropped or the duration of the task is significantly reduced by applying more people or machinery to the task, the dates will move up.

    The gantt chart has been around for a long time, but it is still quite useful to show progress.

    Cheers,
    B

  9. Shyam says:

    I like the idea but seems bit complicated in case of long projects involving numerous activity.

    Also, reading and explaining is required hence not feasible where plans are just send to audience for approval.

    Cheers
    SY

  10. Peter says:

    Great idea Chandoo,

    When I was reading this idea regarding delivery dates, another thought popped into my mind, how can you show the uncertainty with MONEY!!

    In this case, applies to cost management or even a normal budget, you think?

    Would Box Chart and Gannt Chart help to understand the best case, middle case and worst case when money is spend or planned with these three risks are involved?

    I imagine that this chart could help people who write their budgets get a better understanding of risks affecting their spending.

    Peter

  11. Matthew Galman says:

    Chandoo,

    I like it. How would you display an entry once it has been completed (actual)?

    Thank you,

    Matt

  12. Phil says:

    From what you have shown so far I think that this box Gantt chart is awesome! I think that this could be an extremely useful tool.

    I can't wait to learn how to make my own charts in Excel.

    Will the methods that you are going to teach us work in 2003 as well?

  13. [...] Firday, we proposed a new chart for showing project plans. I chose an ugly name for it and called it Gantt Box [...]

  14. TommyZ says:

    You need to read Eli Goldratt's Critical Chain. The uncertainty you are looking for should be accounted for in a project buffer. Not at each task level.

    Further you should spend time understanding Agile Development. This would have you plan only in 1-3week iterations. This allows you to embrace changes to work not yet started, and for your customer to re-direct your course at regular intervals (after each iteration) throughout your project. keyword search: Agile Scrum

    These items will show you that you are solving a tracking problem for something that you can entirely avoid!

  15. […] Chandoo.org’s  Gantt Box Chart. […]

Leave a Reply