How to make a readiness monitoring using a simple wellness questionnaire.
Although I promised I will show you how to visualize the data using screen cast – I won’t do it. I started taping the screen and it took me more than one hour to actually explain basics, without going into advanced techniques, like name references, dynamic graphs and dash boarding (which involves a lot of use of functions like VLOOKUP, HLOOKUP, INDEX and MATCH).
Editing would take even longer, since I am not so comfortable with these advanced techniques yet and I would need to reference help files or websites for formulas, parameters, etc, and that would need to be cut from the video, or you would just need to endure my Directors cut version :)
What I did instead, is I created a template which you can download at the bottom. Here is the screen of the dashboard.
I’ve left more than 150 empty spaces for you to fill with your own data, and I have provided 5 example surveys just to show you the how the visualizations and dashboard works.
To create visualizations of the data, I’ve used Excel 2010 sparkline function, that shows 10 last measurements for each player or whatever you decide in the “Show last” box (that’s the power of dynamic graph).
The problems with sparklines is that there is no absolute zero (and no axes), but rather it depicts range between two end-points, so a small change end up looking huge. That’s why it is important to check numbers as well.
If you are interested in these topics you can check the following sources that will help you out:
The following book really looks interesting. I have it home, glanced through it, but haven’t read it yet. I’ve heard good recommendations about it.
One more idea that might give more information would be to classify surveys based on how many days taken (1) after a game and (2) before the next game. This could be done for the whole squad and/or for each athlete (since teams have reserves and bench guys who might play U21 game or reserve league on a different day). This could give us more power to compare data based on the context (when it was taken during the week taking into consideration previous and next game) instead of just putting them in one basket.
To do this I think we could use Pivot Tables, but at the moment I am learning that skill myself. Maybe one day I will blog about how to do it, but for now I am finishing this series and I am heading toward other projects that can’t wait. One of those projects includes a beep test I am designing, called IE20-10. I am about to alpha test it on me and then on some athletes. After that I will probably give it for free here.
And here is the link for download (both collection sheet and Excel table) on my DropBox account: