• 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.

Cricket Dashboard (new ideas)

ashish mehra

Active Member
Hi,

I am in the process of making Cricket Dashboard. I am in early stages & I would like to know your feedback.

I want to highlight the following stats:

Most Runs
Most Hundreds
High scores
Highest Averages
Highest Strike Rates
Most ducks

Most Wickets
Best Bowling Figures
Best Averages
Best Economy Rates
Best Strike Rates
Most Five Wickets

Regards,
AM:)
 

Attachments

  • WC 2015.xlsm
    28.8 KB · Views: 22
Hi Ashish ,

The data that you have in the workbook will not be useful in generating the statistics that you would like to display. What comments are you looking for ?

Narayan
 
Hi Narayan,

Yeah, you are right. Please check the updated data.

Regards,
AM:)
 

Attachments

  • Cricket World Cup 2015.xlsm
    78.3 KB · Views: 21
Hi Ashish ,

This is more like it , though it takes care of only the bowling figures ; you will need a similar file for the batting figures.

There is no need to 4 separate tabs , since it is just a matter of sorting the data on the respective column.

Narayan
 
Hi Narayan,

Please check the layout of the dashboard.

Regards,
AM:)
 

Attachments

  • Cricket World Cup 2015 - updated.xlsm
    122.7 KB · Views: 19
Hi Ashish ,

This will take some time to evaluate ; however 2 quick points :

1. The dashboard cannot be just a tab ; it has to be a presentation. You will have to bring all the charts together , along with their slicers / dropdowns / other controls , so that only the displays are available to view. All of the pivot tables and other data columns will have to be moved out to a remote area of the tab.

2. I was taking a look at the winning percentage , and I would suggest a different view ; if you look at South Africa's percentage , it is 64% , which tells a completely false story.

I think how these percentages should be worked out is as follows :

Total the number of matches in every World Cup that a team has played ; suppose South Africa has participated in 4 World Cups , and the total number of matches a winning team would have played in each tournament is 9 ; this means the total number of matches a team would have played in those 4 World Cups would be 36 ; if a team was a winner in all 4 of them , it could have won all 36 , or it would have won a lesser number , due to losing matches in the league stage or in matches being abandoned. What ever be the case , dividing the number of matches won by 36 would give the correct percentage , and you would find that the top 5 winners would have the top 5 percentages.

If you see the present percentages , New Zealand has a winning percentage of 59 % , without being either the winners or runners-up even once , while Sri Lanka have won the Cup once , but have a winning percentage of just 49 %.

Narayan
 
Ashish, Narayan

I will respectfully disagree with Narayan's first point about Dashboards not being a Tab

One of the greatest Dashboards I have ever seen, was a series of 10 Tab's

The Tabs were Hidden

The top 10 rows of every page were similar, and included controls and summary data.

Page movement was controlled by Controls on the worksheets and movement between pages appeared seamless, due to the lack of tabs and similarity of the worksheets, color, styles etc

I originally thought they had used addin's to add the control to Excel, but it was all pure Excel.

It worked so seamlessly that I actually struggled to understand how that had achieved the effect until I pulled it apart

Each tab had its own story depending on what you wanted to see, but you never realised that you were changing tabs to navigate, it appeared that the data was just changing before your eyes based on the selections you made along the way
 
Hi Hui ,

My comment was not about the dashboard being a physical tab , which it can very well be.

If you open the uploaded workbook , you will see what I meant.

Narayan
 
Hi Ashish ,

This will take some time to evaluate ; however 2 quick points :

1. The dashboard cannot be just a tab ; it has to be a presentation. You will have to bring all the charts together , along with their slicers / dropdowns / other controls , so that only the displays are available to view. All of the pivot tables and other data columns will have to be moved out to a remote area of the tab.

2. I was taking a look at the winning percentage , and I would suggest a different view ; if you look at South Africa's percentage , it is 64% , which tells a completely false story.

I think how these percentages should be worked out is as follows :

Total the number of matches in every World Cup that a team has played ; suppose South Africa has participated in 4 World Cups , and the total number of matches a winning team would have played in each tournament is 9 ; this means the total number of matches a team would have played in those 4 World Cups would be 36 ; if a team was a winner in all 4 of them , it could have won all 36 , or it would have won a lesser number , due to losing matches in the league stage or in matches being abandoned. What ever be the case , dividing the number of matches won by 36 would give the correct percentage , and you would find that the top 5 winners would have the top 5 percentages.

If you see the present percentages , New Zealand has a winning percentage of 59 % , without being either the winners or runners-up even once , while Sri Lanka have won the Cup once , but have a winning percentage of just 49 %.

Narayan

Hi Narayan,

Ans 1) Yes, I agree we have to bring all the charts to present it as a dashboard.

My question: For people using 2007 version of excel Slicer is not an ideal option. What alternative should we adopt?

Ans 2) You are right South Africa’s winning percentage is 64%; here data is sorted on Winner column & not on Winning % column. We can sort by Winning % & show a complete story.

I have insert new tab i.e. Result Summary kindly refer it.

The Winning % has nothing to do with the number of times the team has won the trophy.

Regards,
AM:)
 

Attachments

  • Cricket World Cup 2015 - updated.xlsm
    129.2 KB · Views: 13
Hi Ashish ,

I suggest you make a layout sketch first , indicating what kind of displays you want to have in which positions , along with the kind of user controls that will be available.

Once this sketch is available , decisions can be made as to the chart types that can be used for the relevant displays ; where ever only values are to be displayed , whether they will be displayed in worksheet cells or in textboxes ; based on the kind of user controls , you can decide whether slicers are the only possibility or other controls can be used ; there is almost nothing that VBA cannot achieve , though the look and feel of slicers and their ease of implementation and use may not be possible using VBA and other controls.

After this , we can start with the derivation of the values which are to be displayed , and look at what kind of data processing will be needed to arrive at the values.

Thus , the actual development of the dashboard in the worksheet tab is the last step.

Narayan
 
Hi Ashish ,

Regarding your point about NZ's winning percentage , let us agree to differ.

As far as I am concerned , any presentation has to tell a story , present a viewpoint ; viewers may or may not agree with the viewpoint , but the presentation should hope to convince them.

When I present statistics on teams , what am I desirous of presenting ? Do I want to show the strongest teams or do I want to display some statistics ?

If it is the former , then we need to think how we can identify the strongest teams ; obviously performance and / or results are the main factor. Performance can be evaluated in various ways , depending on your creativity ; results cannot be manipulated in such a fashion.

My point in talking about NZ was because the winning percentage of 59 % vs. Sri Lanka's winning percentage of 49 % presents what I think is a lopsided picture ; in the initial years of the World Cup , Sri Lanka performed very poorly , but they have placed second twice and been champions once , whereas NZ has never even attained runner-up position. It may be a matter of debate as to which of them is the better side.

Narayan
 
Hi Narayan,

Please check the Sketch tab.
1) On top there are Batsman Stats will update & on bottom there will be Bowlers stats.

2) In between we can show various stats via charts which will include all the records of WC history.

3) As we click on Refresh button (black color rectangle) it will fetch the record & show the updated result. The Dashboard will be interactively updated.

For every tab there is a link.

Regards,
AM:)
 
Hi Ashish ,

Regarding your point about NZ's winning percentage , let us agree to differ.

As far as I am concerned , any presentation has to tell a story , present a viewpoint ; viewers may or may not agree with the viewpoint , but the presentation should hope to convince them.

When I present statistics on teams , what am I desirous of presenting ? Do I want to show the strongest teams or do I want to display some statistics ?

If it is the former , then we need to think how we can identify the strongest teams ; obviously performance and / or results are the main factor. Performance can be evaluated in various ways , depending on your creativity ; results cannot be manipulated in such a fashion.

My point in talking about NZ was because the winning percentage of 59 % vs. Sri Lanka's winning percentage of 49 % presents what I think is a lopsided picture ; in the initial years of the World Cup , Sri Lanka performed very poorly , but they have placed second twice and been champions once , whereas NZ has never even attained runner-up position. It may be a matter of debate as to which of them is the better side.

Narayan
Hi Narayan,

I already agreed to your point (post# 12), we can show Winner column & Winning % column (refer to Result Summary tab)

upload_2015-3-3_15-10-31.png

Also, I am not making my own calculations. I am following ICC.;)

Regards,
AM:)
 
Hi Ashish ,

The idea is good , but I would suggest that instead of hyperlinking to individual tabs , you mimic Chandoo's templates where the dashboard displays the top 1 / top 3 / top 5 and a button labelled More ; clicking this button takes the viewer to the individual tab for more details.

Second , instead of mixing up the displays , see if you can segregate the indicators into 3 categories viz. Teams , Batsmen and Bowlers ; within each category you can have up to 5 indicators.

Narayan
 
Hi Narayan,

Please check the file in which I have used Web scraping to extract the result.
How can we reduce the processing time?

Regards,
AM:)
 

Attachments

  • test.xlsm
    16.3 KB · Views: 9
Hi Narayan,

Please ignore the post# 24.

The ideal step is to run macro 1 & 2 & then copy the data from sheet 1 to OldWc.

Regards,
AM:)
 

Attachments

  • test.xlsm
    39.4 KB · Views: 13
Back
Top