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.
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.
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.
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.
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.
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:
31 Responses to “Beautiful Budget vs. Actual chart to make your boss love you”
Would be considerably easier just to have a table with the variance shown.
On Step 3, how do you "Add budget and actual values to the chart again"?
There are a few ways to do it.
Easy:
1) Copy just the numbers from both columns (Select, CTRL+C)
2) Select the chart and hit CTRL+V to paste. This adds them to chart.
Traditional:
1) Right click on chart and go to "select data..."
2) From the dialog, click on "Add" button and add one series at a time.
One more way to accomplish it is just select the columns into chart. Press Ctrl+C and then press Ctrl+V
Regards
Neeraj Kumar Agarwal
Unfortunately, this doesn't seem to work for me in Excel 2010. The "Var 1" and "Var 2" columns cannot combine two fonts to display the symbol and the figure side-by-side.
Secondly, there is no option to Click on “Value from cells” option when formatting the label options. The only options provided are Series Name, Category Name or Value.
@TheQ47... the emoji font also has normal English letters, so if you use that font, then you should be ok. I am assuming your computer doesn't have that font or hasn't been upgraded for emoji support.
Reg. Excel 2010, you can manually link each label to a cell value. Just select one label at a time (click on labels, wait a second, click on an individual label) and press = and link it to the label var 1 or var 2.
I am using excel 2010, please explain how to apply Step 12
Regards
Neeraj Kumar Agarwal
Hi Neeraj,
"Value from cells" option is only available in Excel 2013 or above. In older versions, you have to manually adjust the label value by linking each label seperately.
Read this please: https://chandoo.org/wp/change-data-labels-in-charts/
Sir, you are just awesome.
Your creativity has no limit.
Regards
Neeraj Kumar Agarwal
Hi Chandoo,
I just found your website, and really love it. It helps me a lot to be an Excel expert 😉
Currently I am facing with a problem at step 11:
Var1 Var2
D30%
A5%
B0%
B4%
B7%
C10%
C13%
D27%
I42%
Though at mapping table, I used windings, here formula uses calibra. How I can change it? I am able to change only the whole cell. In this case numbers will be Windings too.
Thanks for your help!
Hi Mariann... Welcome to Chandoo.org and thanks for your comment.
If you wanted to use symbols from wingdings and combine them with % numbers, then you need to setup two labels. One with symbol, in wingdings font and another with value in normal font. Just add the same series again to the chart, make it invisible, add labels. You may need to adjust the alignment / position of label so everything is visible.
[…] firs article explains how you can enhance your charts with symbols. You can simply insert any supported symbol into your data and charts. To some extend you can […]
You're a good person, thank you to share your knowledge with us, I will try to do in my work
Great visualization of variance. My question is that is this possible in powerbi?
How would you go about it?
HELLO, WHY CANT I FIND VALUES FOR LABELS IN EXCEL 2013
Dear chanddo sir,
What to do if we have dynamic range for Chart. How this will work. can you able to make the same thing works on dynamic range.
Sir Chandoo,
Good Day!
First, I'd like to say that I am very grateful for your work and for sharing all these things with us.
I tried to do this chart but it seems that the symbols don't work with text (abs(var%),"0%") unless we keep the Windings font style.
The problem is, it converts the text into symbol as well and you wont see the 0% anymore. I'm using Windows 7.
WOW - Segoe UI Emoji
This is the greatest discovery for me this month 🙂 Thanks for sharing.
Here's my two-cents:
https://wmfexcel.com/2019/02/17/a-compelling-chart-in-three-minutes/
Sir This is awesome chart, and very easy to made because of your way to explain is very simple , everyone can do. Thank you
one problem i am facing, I hv made this chart , but when i am inserting data table to chart it is showing two times , how can i resolve this
in this chart when i am adding new month data for example first i made this chart jan to mar but when i add data for the apr month graphs updated automatically but labels are missing for that new month
Hi Renuka,
Please make sure the formulas for labels are also calculated for extra months. Just drag down the series and set label range to appropriate address.
So I am playing with the Actual chart here - but amounts are bigger than your - you have 600 as Budget - my budget is 104,000 - is there a way to shorten that I am unaware of
thank you - I LOVE YOUR SITE
Thanks for the tips and tricks on Excel. In the Planned versus Actual chart examples, you use multiple values (ex. multiple Categories in above). How can this be done when we have only 1 set of values? For example if I have only this:
Planned Actual
SOW Budget 417480 367551
How can I create a single bar chart like the one above?
Thank you Chandoo.
This one is just perfect for my Quarterly Review presentation on Operational Budget against Actual Performance for the Hospital I'm currently working with.
Just Subscribed today (10 minutes ago)
Is there a way to make the table of data into a pivot table to be able to add a slicer for the graph due to many different categories and months?
Hi, I tried to modify you template with something appropriate for me, and I found a problem. this template was modified by me started with excel 2010, then 2016 and finally 2019. Same thing - somehow appear an error - or didn't show the emoticons for positive percentage or doubled the emoticons for some rows. I suspect to be from excel. if is need it I can sand you my xlsx for study. Please help if you can.
Hi Chandoo,
Could you please check the Var Formula in Step1. You have mentioned budget-actual and when i did this i got different values but when reversed like actual-budget i got the actual value what you have demonstrated in step1.
Please share your view.
This is a great chart (budget vs. actual). However, in trying recreate it, I cannot color in the UP Down bars individually, and they all become formatted with the same color. I'm using Office 365. Look forward to the feedback.
Thanks.
Dan
pls explain in detail step 7
While in the Excel sheet you have used following formula for Var
Var = Actual - Budget
But
in the note, you have written
Var = Budget - Actual
Good Presentation and Data information.thank you so much chandoo.