• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

GOAL of World Cup

Great Job Semendra

Have you thought about writing a post on how to make this chart in a step by step manner ?
 
Hui said:
Great Job Semendra

Have you thought about writing a post on how to make this chart in a step by step manner ?
Click to expand...

+1

Please do that Somendra. Many will benefit from your instruction.
 
Somendra

An observation, your last 4 data points (end 4 grey points at the 119 and 120 mark) can never be populated. There is an issue in the worksheet change event. At the consolidated level and at the individual level (eg - Germany scored in the 120th minute) the data can't feed through.

This will correct the issue.

Code: 
    For i = 2 To Sheet5.[aa4].Value Step 2
        Sheet5.[z4] = i
        DoEvents
    Next i

The above would replace the code you have in there currently.

Take care

Smallman
 
@Smallman

Thanks for the catch. With so many points I think I missed the last one :). To Speed up the animation a bit I had put the Step 3, which were puting the final value as 118 in the calculation, where as it should be 120. I had changed it to Step 1 to bring it.

Your code is also nice.

@Hui / @r1c1

Will do the same in a day or two.

Updated file is attached.

Regards,
 

Attachments

  • GoalModified.xlsm
    984.9 KB · Views: 21
Hi All,

Here is a small write up about how I made this graph. I had not included the codes in it, if any body has any difficulty in understanding it they can write back.

Suppose you want to show distribution of Goals scored by teams in FIFA World Cup from 1930 to till date minute wise through a Chart in Excel, than this is one of the way. The idea of this chart came to me by one of the article here at GOOOOOAAAAAAAALLLLLL! An Interactive Chart by The Economist.

It’s basically a X-Y (Scatter Chart) with X-axis as minutes and Y- axis as no. of goals in the particular minute.

So to create such such a chart we need data – Minute wise goals scored by counties.

So here are the steps to create such Graphs.

1. Data Collection
2. From the data get X – Value and Y Value
3. To plot the graph based on these X – Value and Y – Value
4. To make the graph dynamic
5. To pop up match details in the graph for each goal
6. Some animation in the chart (Additional Cosmetic will nothing add in the analysis)

Step 1: Data Collection

There can be many ways for collecting data. Here what I did. I went to http://www.thesoccerworldcups.com/ there you have the data for each world cup. I went through each world cup one by one and downloaded the data in Excel using “Get External Data” and selecting “From Web feature”, which allows importing tables present on the selected web page and also allows updating data when you refresh the connections. SO here refresh can be only done for current world cup. (But I did not make it dynamic and make it static as on posting date). After that it’s only to make a table with Team Details, Scoring details Minute wise goal and count of goal minute wise, type of goal (normal, penalty or Own goal) in chronological order.

Step 2: From the data get X – Value and Y Value

So now if you had this accumulated data in chronological order in side of minute you can put a COUNTIF function which will count goals per minute by making the Criteria range as dynamic say $M$2:M2 and using the minute as criteria, so the formula will look like =COUNTIF($M$2:M2,M2). So X – values here are minute and Y- Values are count per minute. Now here the max value of X is 120. Y will vary as per data.

Step 3 & Step 4: Now using this X and Y Value I plot a Default X Y graph (Grey Color) which will be there all the time. Next I put three dropdowns Country, Year & Stage. Based on this selection I extracted data from the main table using advanced filter through a VBA CODE in Worksheet Change Event for these three drop down. So table no two will have extracted data, a fresh count now has to be applied minute wise as we had done in Step 2. Next I created 3 series with IF function to get three series for normal/penalty/Own goal and added these series to our parent chart. And format them as per our need. These three series will also help to get a count of each type which can be displayed above the chart as I did. So this way you will always have a default series (Gray) than 3 more series as per selection. The series can be seen in Chart data selection dialogue box.

Step 5: To pop up match details in the graph for each goal when the person click on any goal we need VBA to get chart X and Y value, which is done through a Class Chart Mouse down event module, which will get the value and assign to cells on a sheet, next by combing this X & Y value say 1-2 will be a unique value is done for each value of extracted data we for our message that we need to pop up through INDEX-MATCH function and then we will put this value in a text box assign some formatting to text box. SO whenever a point is clicked the message which is available on chart as picture using camera tool will make the picture visible and when you click on the picture it will make it invisible which is also done through a VBA code. All the code can be seen in VBA editor.

Step 6: To add animation, it just to loop a number say from 1 to 120 and generate our series for charts slowly, which is again done through VBA. The code for this is with the worksheet change event code.

That’s it! The chart is ready.

I know the explanation is bit confusing, if you had problem in understanding any step, write back I will try to clear it.


Regards,
 
You must log in or register to reply here.
Back
Top