Using the same dataset, I’ve shown in my earlier blog: Scattered Data To A Butterfly, Or A Tornado! http://flyingsalmon.net/blog/?p=2194
on how to present similar information visually and add interactivity, in this post, I’ll show other ways to present the information visually and walk through the pros and cons of each. I won’t be using any formulas to extract data (we’ll still use them for calculating min/max/avg however) but let pivot tables do most of the data shaping work for us.
The data: Raw data of member sign-ups by gender, age for each sport offered.
Objective: We want to get a feel for male and female age ranges in each sport.
Based on the data, I create a pivot table that first shows the average ages for each sport by gender.
Based on the pivot table, we can create a chart as below.
This is okay if we only want to see averages. Of course, we could go back and repeat the same steps but instead of averages, do another one for min, another for max.
But before we can do that, we’ll need to get the min/max also for each gender, per sport. To do this without having to write formulas (as I did in earlier blog linked above), we can create pivot tables again. Instead of Average, specify Minimum and Maximum for each table so that we have 3 pivot tables with min/max/avg for male and female for all sports. We can summarize the results in a single table for a cleaner report as below:
Based on that, I can create a single chart showing all the information we want as below:
This shows a lot of key information in a single chart. We could add slicers to it for interactivity.
But it seems a little crowded for my taste. All I want to is to show the same information more clearly…if I have to break it up by gender, I’m very comfortable with that as long as each visualization is clearer in communication.
I can actually use a chart typically shown for stock prices! Instead of High price, I’ll use Max age. Instead of Close price, I’ll show Average age. And instead of Low price, I’ll use data for Min age. To do that, I need to shape the dataset accordingly. The shape now looks like this:
Finally, the charts that come off it are clear and concise as below:
For example, we clearly notice that among females, in baseball, basketball, football the variations in ages are very low. The largest variation is in tennis. In males group, we see basketball with the largest range. And we can exactly tell what the ages are at any given point.
⇒ Note that the dataset is all made up by my random generator and does not represet actual survey/observation data and used purely for technical illustration.
▛ This post is not meant to be a formal tutorial, instead it is to offer key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed. If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible. At the very least, it’ll encourage me to share more posts/tips/knowledge in the future. ▟