I’m happy to host this guest post from Mynda Treacy of MyOnlineTrainingHub. Mynda writes about and teaches Excel skills, ranging from number formats to chart types and Excel Dashboards. The example Mynda writes about here is also being cross-posted on the HelpMeViz website. If you want to discuss the graphic choices or propose your own, please head over there.

Sporting data is a treasure trove of statistics but one of the challenges is finding the best way to present the data and get your message across quickly and easily.

These stats from a basketball game are a great example of potentially useful data that can easily get lost in the wrong chart:

player_impact_data_1

The question: which players are most effective?

A quick note on Impact; this is the sum of points scored for the team minus the points scored against the team while that player was on the court. An overall positive impact means the team scored more points ‘for’ than ‘against’ while that player was on the court. One limitation of Impact is that it doesn’t take into consideration the other players on the court at the same time.

Caveat – this is data from an amateur game. In a professional environment the Impact would be a more complex calculation.

Chart Options

Bubble Chart

player_impact_bubble_2

I’ll say it; I’m not a fan of bubble charts.

The main gripe with them is it’s not that easy to compare the size of the different bubbles (hence the labels for clarity), however with this data it also causes a few more problems:

  1. In the chart above I used the Impact to determine the size of the bubble; bigger = better. This made more sense than using the time for bubble size since more time on court doesn’t necessarily equate to better.
  2. The downside of this logical application means we can’t plot the negative impact bubbles in a sensible way, as you can see with player 4. Excel gives the negative bubble a different colour but without also adding a label you wouldn’t know what that signified.
  3. Then there’s player # 13…there isn’t one, but you need to end your axis at 13 otherwise player # 12’s bubble gets cut in half.

Ugh, delete the bubble chart.

Column Chart with Secondary Axis

player_impact_column_3

Secondary axes can be a contentious issue among data viz gurus, with most of them preferring to avoid them where possible due to their potential to confuse the audience, among other things.

This example has one other problem; because the time scale on the secondary axis begins in line with the start of the impact scale, which is this case is a negative value, it effectively closes the gap between Impact and time on court. This isn’t necessarily a problem except if you were to compare data from one match to the next then you’d need to be careful that all axes started from the same point.

Scatter Chart

player_impact_scatter_4

I feel a bit ‘meh’ about this chart. While it’s better than the previous charts in that it depicts the relationship between the impact and time on court, it still doesn’t give any categorical answers as to which player gives the biggest bang for buck.

Bang for Buck Chart

player_impact_bar_5

This bar chart is what I like to call the ‘Bang for Buck’ chart.

Bang = Impact

Buck = Time on Court

From this chart we can clearly see that the impact of player 12 is highest at 2 goals per minute, with players 8 and 5 returning the next biggest bang for buck.

If you now look at any of the previous charts you can see that it is difficult to identify player 8 as one of the most effective on the team without doing some more mental arithmetic (12 divided by 9min 35 sec …um….um…ugh, where’s my calculator?).

Note: I calculated the values for the bars by dividing the impact for each player by the minutes on court (converted to decimals with *24*60):

player_impact_formula_6

Download the Excel File

impact_vs_time_on_court.xlsx

Mynda_PhotoAbout Mynda — I am co-founder of MyOnlineTrainingHub, I love data and Excel is my weapon of choice for making sense of it. If you want to learn from me you can. I share my knowledge on my Excel blog: http://www.myonlinetraininghub.com/blog, and in my Excel Dashboard course: http://www.myonlinetraininghub.com/excel-dashboard-course>/a>